Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.1Mb
PDF (A4) - 43.2Mb
Man Pages (TGZ) - 295.4Kb
Man Pages (Zip) - 400.6Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Logical Operators

14.4.3 Logical Operators

Table 14.5 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.

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
  • NOT, !

    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.

  • AND, &&

    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.

  • OR, ||

    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
    Note

    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 concatenation.

  • XOR

    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).