Skip navigation links
11.3 Control Flow Functions »
Section Navigation      [Toggle]

11.2.4. Logical Operators

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

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

  • 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 && 1;
        -> 1
mysql> SELECT 1 && 0;
        -> 0
mysql> SELECT 1 && NULL;
        -> NULL
mysql> SELECT 0 && NULL;
        -> 0
mysql> SELECT NULL && 0;
        -> 0

    Please note that MySQL versions prior to 4.0.5 stop evaluation when a NULL is encountered, rather than continuing the process to check for possible 0 values. This means that in these versions, SELECT (NULL AND 0) returns NULL instead of 0. As of MySQL 4.0.5, the code has been re-engineered so that the result is always as prescribed by the SQL standards while still using the optimization wherever possible.

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

    XOR was added in MySQL 4.0.2.


User Comments

Posted by Chris Lacy-Hulbert on June 2 2003 6:52am[Delete] [Edit]

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

Posted by Matt on September 19 2003 12:35pm[Delete] [Edit]

An alternative to your use of XOR is ABS(x-1) if you are using 0 and 1 switches.

Posted by Joël Larose on October 20 2003 12:40pm[Delete] [Edit]

Or you could just use the NOT operator, as in

update mytable set mytable.switch=NOT mytable.switch where [condition];

Posted by W L on October 19 2005 7:32am[Delete] [Edit]

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.

Posted by Toby Thain on April 6 2006 10:56pm[Delete] [Edit]

Or just '1-X'

Posted by Marnen Laibow-Koser on May 24 2006 6:22pm[Delete] [Edit]

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.