MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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
.
MySQL evaluates any nonzero, 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
.
The !
, operator
is a nonstandard MySQL extension. As of MySQL 8.0.17, this
operator is deprecated; expect it to be removed in a future
version of MySQL. Applications should be adjusted to use the
standard SQL NOT
operator.
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 AND 1;
-> 1 mysql>SELECT 1 AND 0;
-> 0 mysql>SELECT 1 AND NULL;
-> NULL mysql>SELECT 0 AND NULL;
-> 0 mysql>SELECT NULL AND 0;
-> 0
The &&
,
operator is a nonstandard MySQL extension. As of MySQL
8.0.17, this operator is deprecated; expect support for it
to be removed in a future version of MySQL. Applications
should be adjusted to use the standard SQL
AND
operator.
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 OR 1;
-> 1 mysql>SELECT 1 OR 0;
-> 1 mysql>SELECT 0 OR 0;
-> 0 mysql>SELECT 0 OR NULL;
-> NULL mysql>SELECT 1 OR NULL;
-> 1
If the PIPES_AS_CONCAT
SQL mode is enabled,
||
signifies
the SQL-standard string concatenation operator (like
CONCAT()
).
The ||
, operator
is a nonstandard MySQL extension. As of MySQL 8.0.17, this
operator is deprecated; expect support for it to be removed
in a future version of MySQL. Applications should be
adjusted to use the standard SQL
OR
operator. Exception:
Deprecation does not apply if
PIPES_AS_CONCAT
is enabled
because, in that case,
||
signifies
string concatentation.
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)
.