6.3.1.3 Logical Operators

In SQL, all logical operators evaluate to TRUE, FALSE or NULL (UNKNOWN). In MySQL, this is implemented as 1 (TRUE), 0 (FALSE), and NULL. Most of this is common between different SQL databases, however some may return any non-zero value for TRUE.

NOT

!

Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is non-zero, 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 non-zero 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 0s. This means that in these versions, SELECT (NULL AND 0) returns NULL instead of 0. In 4.0.5 the code has been re-engineered so that the result will always be as prescribed by the SQL standards while still using the optimisation wherever possible.

OR

||

Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned.

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 non-zero, otherwise 0 is returned.

 example_for_help_topic XOR
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 version 4.0.2.

User Comments

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

Add your own comment.