MySQL :: MySQL 8.4 Reference Manual :: 14.4.3 Logical Operators (original) (raw)

14.4.3 Logical Operators

Table 14.5 Logical Operators

In SQL, all logical operators evaluate toTRUE, FALSE, orNULL (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 forTRUE.

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 extension, and is deprecated; expect it to be removed in a future version of MySQL. Applications, where necessary, should be adjusted to use the standard SQLNOT operator instead.

• AND,&&
  Logical AND. Evaluates to 1 if all operands are nonzero and not NULL, to0 if one or more operands are0, 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 extension and is deprecated; expect support for it to be removed in a future version of MySQL. Applications, where necessary, should be adjusted to use the standard SQL AND operator instead.

• OR,||
  Logical OR. When both operands are non-NULL, the result is1 if any operand is nonzero, and0 otherwise. With aNULL operand, the result is1 if the other operand is nonzero, andNULL otherwise. If both operands areNULL, the result isNULL.

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 (likeCONCAT()).
The ||, operator is a nonstandard extension, and is deprecated; expect support for it to be removed in a future version of MySQL. Applications, where necessary, should be adjusted to use the standard SQL OR operator instead. Exception: Deprecation does not apply ifPIPES_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 to1 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).