As we stated at the beginning of this chapter, our central premise is that it is almost always the best course of action to make the database perform as much work as possible, thus minimizing the amount of filtering and manipulating data that you do in your programming code. Simply put, a strong working knowledge of operators increases your options when it comes to replacing your programming logic with SQL logic.
Most of MySQL’s arithmetic and logical operators are likely to be familiar to you, as they’re more or less the same as you’ll find in most common programming languages. So we won’t spend a great deal of time with them, but we will go over them and point out their MySQL-specific eccentricities where necessary.
MySQL has the basic logical operators you would expect to find in any scripting or programming language. The list was completed in MySQL 4.0.2, when support for XOR was added.
Logical operators always evaluate to TRUE, FALSE, or NULL. As we’ve mentioned before, MySQL doesn’t have a Boolean datatype as such; instead, it uses 1 for Boolean TRUE and 0 for Boolean FALSE.
NOT (!): Negates the condition it precedes. Generally speaking, MySQL doesn’t care whether you use ! or NOT , so you can write whichever suits you.
AND (&&): In order for an AND comparison to be true, both conditions to be tested must be true. You may use AND and && interchangeably. Note that 1 AND NULL returns NULL , and 0 AND NULL returns 0 (FALSE).
OR (||): Is true if at least one of the operands is true. 1 OR NULL returns 1 (TRUE), and 0 OR NULL returns NULL . OR and || are synonyms, so you can use either of them as you prefer.
XOR (^): Yields a true result if one and only one of the operands is true. Any value XOR ’ed with NULL yields NULL . XOR and ^ are synonyms.
We recommend that you use AND and OR rather than && and || in order to preclude the possibility of accidentally leaving out one of the doubled symbols and so writing a bitwise operator instead of a logical operator.
In addition, the double-pipe version of OR also serves as a concatenation operator when running MySQL in ANSI mode, and it can be used in this manner in Oracle and PostgreSQL as well.
The rules for resolving expressions containing logical operators are shown as in Table 4-1.
Table 4-1. Truth Tables for MySQL Logical Operators
While you may have seen these before, you may not be familiar with how they act on NULL values in MySQL. Basically, the negation of NULL is NULL , and NULL used with any logical operator and any other value also results in NULL , with one exception: A value of FALSE, when AND ’ed with NULL , results in a Boolean FALSE.NULL values in MySQL. Basically, the negation of NULL is NULL , and NULL used with any logical operator and any other value also results in NULL , with one exception: A value of , when AND ’ed with NULL , results in a Boolean . While you may have seen these before, you may not be familiar with how they act on NULL values in MySQL. Basically, the negation of NULL is NULL , and NULL used with any logical operator and any other value also results in NULL , with one exception: A value of , when AND ’ed with NULL , results in a Boolean .
As in the case of logical operators, MySQL’s arithmetic operators are fairly standard. Here they are, along with a few “gotchas” to watch for:
+ (addition / unary positive): Add two numbers together. A unary plus sign can be used to denote a positive number (basically, it is ignored). Note that, unlike the case with some scripting languages and Microsoft SQL Server, you cannot use the plus sign to concatenate strings! (See the notes about type conversion in the next section.)
– (subtraction / unary negative or additive inverse): Subtracts one number from another. Used with a single number, it causes that number to be negative.
* (multiplication): Gets the product of two numbers. The order of the arguments does not affect the value returned.
/ (division): Indicates a floating-point division: for example, 3 / 5 returns the value 0.6.
DIV (integer division): Indicates an integer division; all fractions are rounded down. For example, 5 DIV 3 returns 1, and 3 DIV 5 returns 0. This operator was added in MySQL 4.1.
% (modulus): Performs a division and returns the remainder. For example, 5 % 3 yields 2. If the first argument is negative, a negative value is returned, so that -10 % -3 and -10 % -3 both return –1, but 10 % -3 returns 1. Note that where ABS(a) < ABS(b) , a % b simply returns a; for example, 3 % 10 evaluates to 3, and -3 % 10 evaluates to –3. You may also use the MOD() function for this; for example, MOD(5, 3) returns 2. Beginning with MySQL 4.1, you may also write 5 MOD 3 instead of MOD(5, 3) .
ABS(N) returns the absolute value of a number: if N is positive, the returned value is simply N; if N is negative, the function returns –N. In case you’re unfamiliar with absolute values, we show a quick example here.
blog comments powered by Disqus