Optimizing Queries with Operators, Branching and Functions - MySQL Operators
(Page 3 of 6 )
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.
NOTE Most of the functions and operators that we’ll look at in this chapter can be used in SELECT , INSERT , and UPDATE queries, either as column values or in WHERE clauses. We’ll provide you with examples of all of these.
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.
NOTE Like most programming languages, MySQL uses parentheses for grouping to force the order in which operations are performed. You can use these whenever you want to override the normal precedence of MySQL operators, which we’ll discuss later in this chapter.
Logical Operators
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.
NOTE Bitwise operators aren’t used that often, and we don’t cover them here. If you need to find out about them, consult the MySQL Manual or other reference.
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
OPERATOR | TRUE | FALSE | NULL |
AND TRUE FALSE NULL | TRUE FALSE NULL | FALSE FALSE FALSE | NULL FALSE NULL |
OR TRUE FALSE NULL | TRUE TRUE NULL | TRUE FALSE NULL | TRUE NULL NULL |
XOR TRUE FALSE NULL | FALSE TRUE NULL | TRUE FALSE NULL | NULL NULL NULL |
NOT | FALSE | TRUE | NULL |
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 .
Arithmetic Operators
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.
Next: Type Conversions with Logical and Arithmetic Operators >>
More MySQL Articles
More By Apress Publishing
|
 This article is excerpted from chapter four of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324). Check it out at your favorite bookstore today. Buy this book now.
|
|
/ 11 
