Optimizing Queries with Operators, Branching and Functions - Type Conversions in Comparisons
(Page 6 of 6 )
Here are few points to keep in mind when comparing values of different types using any of the comparison operators =, <>, !=, >, >=, <, and <=:
- Comparing any value with the null value returns NULL .
- String comparisons are case-insensitive unless you use the BINARY qualifier in the query or in the definition of the column whose value is being compared: 'a' = 'A' returns 1 (Boolean TRUE), and BINARY 'a' = 'A' returns 0 (Boolean FALSE).
- When comparing a string with a number, any leading spaces in the string are discarded, then any leading digits in the string that remains are converted to a number and any remaining (trailing) characters are dropped, so both 5 < '8a' and ' 15x' = 15 return 1. If the string does not begin with a digit, the string is evaluated as 0, so both the expressions 5 < 'a8' and ' x15' = 15 return 0. And 3.0 >= '2f5' evaluates as TRUE (returns 1) and 14 < '311mft7' is FALSE.
- When comparing strings and numbers, if the string (after any leading spaces are dropped) begins with one or more digits followed by a decimal point and then by one or more digits, any additional characters following the second set of digits are dropped; for example, all three of the expressions '3.28' = 3.28, '3.28.6' = 3.28, and 3.28 = '3.28x' will evaluate to TRUE (1).
Operator Precedence
When multiple operators are involved in a single expression, things can get a bit complicated. For example, how should MySQL resolve an expression like 2 + 3 AND 1? Should this evaluate to 3 (3 AND 1 evaluates to 1; 2 + 1 is equal to 3) or to 1 (2 + 3 is equal to 5; 5 AND 1 evaluates as 1)? Of course, you can test this directly, as shown here:
However, it isn’t really practical to take the time to do this every time you encounter such an expression, not to mention the fact that, in real life, such expressions are likely to be working with values that aren’t known ahead of time. Fortunately, there’s a set order of precedence that determines which operations are to be done first.
The order of precedence for operators in MySQL is almost identical to that found in ANSI SQL. Table 4-2 shows the order of precedence from highest to lowest.
Table 4-2. MySQL Operator Precedence
OPERATOR ( ) | DESCRIPTION Parentheses (force grouping) |
+, -; ~ | Unary plus and minus; bitwise inverse |
*, /, % / MOD() / MOD, DIV | Multiplication and division |
+, - | Addition and subtraction |
<=> | Null-safe comparison |
=, <>, !=, >=, <= | Comparison |
&, |, ^ | Bitwise AND, OR, and exclusive OR (XOR) |
NOT / ! | Logical negation |
AND / && | Logical AND |
OR / ||; IN, SOME, ANY; LIKE | Logical OR; set membership; "fuzzy" (wildcard) comparison |
<<, >> | Bitwise shift |
= | Variable assignment |
If you want operations to be performed in some other order, you can always force the issue by using parentheses.
When evaluating complex expressions involving parentheses, MySQL follows what’s sometimes known as the “inside-outside” rule: operations inside the innermost set(s) of parentheses are performed first, followed by the next set outward, and then by the next set outward from that, and so on. If you’ve written code in practically any other modern programming or scripting language, you should be used to this already.
Please check back next week for the second part of this article.
/ 11 
