Table 11.4. Logical Operators
Name | Description |
---|---|
AND , &&
|
Logical AND |
NOT , !
|
Negates value |
|| , OR
|
Logical OR |
XOR |
Logical XOR |
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 nonzero value for
TRUE
.
Note that MySQL evaluates any nonzero or
non-NULL
value to TRUE
.
For example, the following statements all assess to
TRUE
:
mysql>SELECT 10 IS TRUE;
-> 1 mysql>SELECT -10 IS TRUE;
-> 1 mysql>SELECT 'string' IS NOT NULL;
-> 1
Logical NOT. Evaluates to 1
if the
operand is 0
, to 0
if
the operand is nonzero, 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
.
Logical AND. Evaluates to 1
if all
operands are nonzero 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
Logical OR. When both operands are
non-NULL
, the result is
1
if any operand is nonzero, and
0
otherwise. With a
NULL
operand, the result is
1
if the other operand is nonzero, 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
Logical XOR. Returns NULL
if either
operand is NULL
. For
non-NULL
operands, evaluates to
1
if an odd number of operands is
nonzero, 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)
.
User Comments
XOR is useful for throwing a boolean switch with just a single query. For example:
mysql> update mytable set mytable.switch=1 XOR mytable.switch where [condition];
will toggle a boolean field 'switch' from 1 to 0 or 0 to 1.
Hope that's useful,
christo
An alternative to your use of XOR is ABS(x-1) if you are using 0 and 1 switches.
Or you could just use the NOT operator, as in
update mytable set mytable.switch=NOT mytable.switch where [condition];
Note that the ! operator was on the same level of precedence as the NOT operator, until 5.02, when it is at a higher level.
See the page on "12.1.1. Operator Precedence" for the precedence for all the operators.
Or just '1-X'
The documentation doesn't say anything about this, but it appears (based on some tests I just ran) that MySQL short-circuits evaluation of logical operators just like most other languages do.
Add your own comment.