| |
12.1.4. Logical Operators
In SQL, all logical operators evaluate to
TRUE, FALSE, or
NULL (UNKNOWN). In MySQL,
these are implemented as 1 (TRUE), 0
(FALSE), and NULL. Most of
this is common to different SQL database servers, although some
servers may return any non-zero value for
TRUE.
-
NOT, !
Logical NOT. Evaluates to 1 if the
operand is 0, to 0 if
the operand is non-zero, and NOT NULL
returns NULL.
mysql> SELECT NOT 10;
-> 0
mysql> SELECT NOT 0;
-> 1
mysql> SELECT NOT NULL;
-> NULL
mysql> SELECT ! (1+1);
-> 0
mysql> SELECT ! 1+1;
-> 1
The last example produces 1 because the
expression evaluates the same way as
(!1)+1.
-
AND, &&
Logical AND. Evaluates to 1 if all
operands are non-zero and not NULL, to
0 if one or more operands are
0, otherwise NULL is
returned.
mysql> SELECT 1 && 1;
-> 1
mysql> SELECT 1 && 0;
-> 0
mysql> SELECT 1 && NULL;
-> NULL
mysql> SELECT 0 && NULL;
-> 0
mysql> SELECT NULL && 0;
-> 0
-
OR, ||
Logical OR. When both operands are
non-NULL, the result is
1 if any operand is non-zero, and
0 otherwise. With a
NULL operand, the result is
1 if the other operand is non-zero, and
NULL otherwise. If both operands are
NULL, the result is
NULL.
mysql> SELECT 1 || 1;
-> 1
mysql> SELECT 1 || 0;
-> 1
mysql> SELECT 0 || 0;
-> 0
mysql> SELECT 0 || NULL;
-> NULL
mysql> SELECT 1 || NULL;
-> 1
-
XOR
Logical XOR. Returns NULL if either
operand is NULL. For
non-NULL operands, evaluates to
1 if an odd number of operands is
non-zero, otherwise 0 is returned.
mysql> SELECT 1 XOR 1;
-> 0
mysql> SELECT 1 XOR 0;
-> 1
mysql> SELECT 1 XOR NULL;
-> NULL
mysql> SELECT 1 XOR 1 XOR 1;
-> 1
a XOR b is mathematically equal to
(a AND (NOT b)) OR ((NOT a) and b).
|
|
