Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.9Mb
PDF (A4) - 31.0Mb
PDF (RPM) - 30.2Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.3Kb
Man Pages (Zip) - 292.4Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Logical Operators

12.3.3 Logical Operators

Table 12.4 Logical Operators

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

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


User Comments
  Posted by Chris Lacy-Hulbert on June 2, 2003
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
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
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
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
Or just '1-X'
  Posted by Marnen Laibow-Koser on May 24, 2006
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.
  Posted by Dredwerkz Dredwerkz on November 24, 2010
/*
* This is what I did to find out whether both AND, OR, &&, ||, &, | work as short circuit logical operators or not.
*
* Testing OR operand:
*/
SET @my_var := NULL;
SELECT IF(0 < 1 OR @my_var := (1 > 0), 'A', 'B') AS `rslt.`,
@my_var,
IF(0 > 1 OR @my_var := (1 > 0), 'A', 'B') AS `rslt.`,
@my_var;
/*
* The output:
* Query OK, 0 rows affected (0.00 sec)
*
* +-------+---------+-------+---------+
* | rslt. | @my_var | rslt. | @my_var |
* +-------+---------+-------+---------+
* | A | NULL | A | 1 |
* +-------+---------+-------+---------+
* 1 row in set (0.01 sec)
*
* Testing AND operand:
*/
SET @my_var := NULL;
SELECT IF(0 > 1 AND @my_var := (1 < 0), 'A', 'B') AS `rslt.`,
@my_var,
IF(0 < 1 AND @my_var := (1 < 0), 'A', 'B') AS `rslt.`,
@my_var;
/*
* The output:
* Query OK, 0 rows affected (0.00 sec)
*
* +-------+---------+-------+---------+
* | rslt. | @my_var | rslt. | @my_var |
* +-------+---------+-------+---------+
* | B | NULL | B | 0 |
* +-------+---------+-------+---------+
* 1 row in set (0.00 sec)
*
* Testing || operand:
*/
SET @my_var := NULL;
SELECT IF(0 < 1 || @my_var := (1 > 0), 'A', 'B') AS `rslt.`,
@my_var,
IF(0 > 1 || @my_var := (1 > 0), 'A', 'B') AS `rslt.`,
@my_var;
/*
* Query OK, 0 rows affected (0.00 sec)
*
* +-------+---------+-------+---------+
* | rslt. | @my_var | rslt. | @my_var |
* +-------+---------+-------+---------+
* | A | NULL | A | 1 |
* +-------+---------+-------+---------+
* 1 row in set (0.00 sec)
*
* Testing && operand:
*/
SET @my_var := NULL;
SELECT IF(0 > 1 AND @my_var := (1 < 0), 'A', 'B') AS `rslt.`,
@my_var,
IF(0 < 1 AND @my_var := (1 < 0), 'A', 'B') AS `rslt.`,
@my_var;
/*
* The output:
* Query OK, 0 rows affected (0.00 sec)
*
* +-------+---------+-------+---------+
* | rslt. | @my_var | rslt. | @my_var |
* +-------+---------+-------+---------+
* | B | NULL | B | 0 |
* +-------+---------+-------+---------+
* 1 row in set (0.00 sec)
*
* Testing the | operand:
*/
SET @my_var := NULL;
SELECT IF(0 < 1 | @my_var := (1 > 0), 'A', 'B') AS `rslt.`,
@my_var,
IF(0 > 1 | @my_var := (1 < 0), 'A', 'B') AS `rslt.`,
@my_var;
/*
* The output:
* Query OK, 0 rows affected (0.00 sec)
*
* +-------+---------+-------+---------+
* | rslt. | @my_var | rslt. | @my_var |
* +-------+---------+-------+---------+
* | A | 1 | B | 0 |
* +-------+---------+-------+---------+
* 1 row in set (0.00 sec)
*
* Testing the & operand:
*/
SET @my_var := NULL;
SELECT IF(0 > 1 & @my_var := (1 < 0), 'A', 'B') AS `rslt.`,
@my_var,
IF(0 < 1 | @my_var := (1 > 0), 'A', 'B') AS `rslt.`,
@my_var;
/*
* The output:
* Query OK, 0 rows affected (0.00 sec)
*
* +-------+---------+-------+---------+
* | rslt. | @my_var | rslt. | @my_var |
* +-------+---------+-------+---------+
* | B | 0 | A | 1 |
* +-------+---------+-------+---------+
* 1 row in set (0.01 sec)
*
*
* Summary:
* +---------+----------------+
* | Operand | Short-circuit? |
* +---------+----------------+
* | OR | Yes |
* +---------+----------------+
* | AND | Yes |
* +---------+----------------+
* | || | Yes |
* +---------+----------------+
* | && | Yes |
* +---------+----------------+
* | | | No |
* +---------+----------------+
* | & | No |
* +---------+----------------+
*/

Sign Up Login You must be logged in to post a comment.