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):

  • NOT

  • AND

  • OR

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