Logical/Boolean operators¶

Logical operators return the result of a particular Boolean operation on one or two input expressions.

They can only be used as a predicate (e.g. in the WHERE clause). Input expressions must be predicates.

See also:: BOOLAND , BOOLNOT , BOOLOR , BOOLXOR

List of logical/Boolean operators¶

Operator	Syntax Example	Description
`AND`	`a AND b`	Matches both expressions (`a` and `b`).
`NOT`	`NOT a`	Does not match the expression.
`OR`	`a OR b`	Matches either expression.

The order of precedence of these operators is shown below (from highest to lowest):

Examples¶

The first example uses the following table and data:

CREATE TABLE logical (t BOOLEAN, f BOOLEAN, n BOOLEAN);
INSERT INTO logical (t, f, n) VALUES (True, False, NULL);

Copy

Logical operators are typically used in WHERE clauses:

SELECT t, f, n FROM logical WHERE t AND NOT f;

Copy

Output:

+------+-------+------+
| T    | F     | N    |
|------+-------+------|
| True | False | NULL |
+------+-------+------+

Copy

The examples below show the precedence of the logical operators:

The first example shows that the precedence of AND is higher than the precedence of OR. The first expression is evaluated the same way as the second expression, not the same way as the third expression.
SELECT TRUE OR TRUE AND FALSE, TRUE OR (TRUE AND FALSE), (TRUE OR TRUE) AND FALSE;
Copy
Output:
+------------------------+--------------------------+--------------------------+
| TRUE OR TRUE AND FALSE | TRUE OR (TRUE AND FALSE) | (TRUE OR TRUE) AND FALSE |
|------------------------+--------------------------+--------------------------|
| True                   | True                     | False                    |
+------------------------+--------------------------+--------------------------+
Copy
The next example shows that the precedence of NOT is higher than the precedence of AND. The first expression is evaluated the same way as the second expression, not the same way as the third expression.
SELECT NOT FALSE AND FALSE, (NOT FALSE) AND FALSE, NOT (FALSE AND FALSE);
Copy
Output:
+---------------------+-----------------------+-----------------------+
| NOT FALSE AND FALSE | (NOT FALSE) AND FALSE | NOT (FALSE AND FALSE) |
|---------------------+-----------------------+-----------------------|
| False               | False                 | True                  |
+---------------------+-----------------------+-----------------------+
Copy
The next example shows that the precedence of NOT is higher than the precedence of OR. The first expression is evaluated the same way as the second expression, not the same way as the third expression.
SELECT NOT FALSE OR TRUE, (NOT FALSE) OR TRUE, NOT (FALSE OR TRUE);
Copy
Output:
+-------------------+---------------------+---------------------+
| NOT FALSE OR TRUE | (NOT FALSE) OR TRUE | NOT (FALSE OR TRUE) |
|-------------------+---------------------+---------------------|
| True              | True                | False               |
+-------------------+---------------------+---------------------+
Copy

The next few examples show “truth tables” for the logical operators.

Create a new table and data:

CREATE TABLE logical2 (x BOOLEAN);
INSERT INTO logical2 (x) VALUES 
    (False),
    (True),
    (NULL);

Copy

This shows the truth table for the OR operator:

SELECT x AS "OR", x OR False AS "FALSE", x OR True AS "TRUE", x OR NULL AS "NULL"
  FROM logical2;

Copy

Output:

+-------+-------+------+------+
| OR    | FALSE | TRUE | NULL |
|-------+-------+------+------|
| False | False | True | NULL |
| True  | True  | True | True |
| NULL  | NULL  | True | NULL |
+-------+-------+------+------+

Copy

This shows the truth table for the AND operator:

SELECT x AS "AND", x AND False AS "FALSE", x AND True AS "TRUE", x AND NULL AS "NULL"
  FROM logical2;

Copy

Output:

+-------+-------+-------+-------+
| AND   | FALSE | TRUE  | NULL  |
|-------+-------+-------+-------|
| False | False | False | False |
| True  | False | True  | NULL  |
| NULL  | False | NULL  | NULL  |
+-------+-------+-------+-------+

Copy