Logical Operators Reference: AND, OR & NOT Explained - AnalyticDB for PostgreSQL

AnalyticDB for PostgreSQL supports the same logical operators as PostgreSQL. Use AND, OR, and NOT to build complex filter conditions in SQL queries.

For the full PostgreSQL specification, see Logical operators.

Operators

Operator	Syntax	Description
`AND`	`a AND b`	Returns TRUE when both operands are TRUE
`OR`	`a OR b`	Returns TRUE when at least one operand is TRUE
`NOT`	`NOT a`	Inverts the Boolean value of the operand

Operator precedence (highest to lowest):

NOT
AND
OR

Use parentheses to override the default precedence when combining multiple operators in a single condition.

NULL behavior

Logical operations involving NULL do not always return NULL. For example, NULL AND FALSE evaluates to FALSE, and NULL OR TRUE evaluates to TRUE.

The complete truth tables are shown below.

AND and OR truth tables

a	b	a AND b	a OR b
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	NULL	NULL	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	NULL	FALSE	NULL
NULL	NULL	NULL	NULL

NOT

a	NOT a
TRUE	FALSE
FALSE	TRUE
NULL	NULL

Examples

The examples below use a sample table to show how each operator filters rows.

CREATE TABLE orders (id INT, amount INT, status VARCHAR);
INSERT INTO orders VALUES
  (1, 8,  'shipped'),
  (2, 25, 'pending'),
  (3, 15, 'pending'),
  (4, 47, 'shipped');

AND — both conditions must be true

SELECT * FROM orders WHERE amount > 20 AND status = 'pending';

Result:

 id | amount | status
----+--------+---------
  2 |     25 | pending

OR — either condition can be true

SELECT * FROM orders WHERE amount > 20 OR status = 'pending';

Result:

 id | amount | status
----+--------+---------
  2 |     25 | pending
  3 |     15 | pending
  4 |     47 | shipped

Precedence — AND binds tighter than OR

The following query returns rows where status = 'pending', OR where amount = 8 AND status = 'shipped'. AND is evaluated first.

SELECT * FROM orders WHERE status = 'pending' OR amount = 8 AND status = 'shipped';

Result:

 id | amount | status
----+--------+---------
  1 |      8 | shipped
  2 |     25 | pending
  3 |     15 | pending

To evaluate OR first, add parentheses:

SELECT * FROM orders WHERE (status = 'pending' OR amount = 8) AND status = 'shipped';

Result:

 id | amount | status
----+--------+---------
  1 |      8 | shipped

NOT — negates a condition

SELECT * FROM orders WHERE NOT status = 'shipped';

Result:

 id | amount | status
----+--------+---------
  2 |     25 | pending
  3 |     15 | pending