Logical operators (also known as boolean operators) are used to estimate some set of conditions where the result can be TRUE, FALSE, or NULL (missing, unknown, unavailable, unassigned).
There are 3 logical operators available in PostgreSQL:
AND
OR
NOT
These boolean operators are used to match conditions in a SQL statement—e.g., in WHERE and HAVING clauses.
AND = if both boolean expressions are true then it will return TRUE
OR = if any boolean expression is true then it will return TRUE
NOT = Reverses the value of Boolean operator.
AND
AND operator truth table
|
x1 |
y1 |
x1 AND y1 |
|
TRUE |
TRUE |
TRUE |
|
TRUE |
FALSE |
FALSE |
|
TRUE |
NULL |
NULL |
|
FALSE |
FALSE |
FALSE |
|
FALSE |
NULL |
FALSE |
|
NULL |
NULL |
NULL |
Examples
- Both expressions are true, so the result is TRUE.
postgres=# select 1=1 and 2=2;
?column?
----------
t
(1 row)
- One expression is true and another expression is false, so the result is FALSE.
postgres=# select 1=1 and 2=3;
?column?
----------
f
(1 row)
- One expression is true and another expression is NULL, so the result is NULL.
postgres=# select 1=1 and null;
?column?
----------
(1 row)
- Both expressions are false, so the result is FALSE.
postgres=# select 1=3 and 2=3;
?column?
----------
f
(1 row)
- One expression is false and another expression is NULL, so the result is FALSE.
postgres=# select 1=3 and null;
?column?
----------
f
(1 row)
- Both expressions are NULL so the result is NULL.
postgres=# select null and null;
?column?
----------
(1 row)
OR
OR operator truth table
|
x1 |
y1 |
x1 OR y1 |
|
TRUE |
TRUE |
TRUE |
|
TRUE |
FALSE |
TRUE |
|
TRUE |
NULL |
TRUE |
|
FALSE |
FALSE |
FALSE |
|
FALSE |
NULL |
NULL |
|
NULL |
NULL |
NULL |
Examples
- Both expressions are true, so the result is TRUE.
postgres=# select 1=1 OR 2=2;
?column?
----------
t
(1 row)
- One expression is true and another expression is false so the result is TRUE.
postgres=# select 1=1 OR 2=3;
?column?
----------
t
(1 row)
- One expression is true and another expression is NULL, so the result is TRUE.
postgres=# select 1=1 OR null;
?column?
----------
t
(1 row)
- Both expressions are false, so the result is FALSE.
postgres=# select 1=3 OR 2=3;
?column?
----------
f
(1 row)
- One expression is false and another expression is NULL so the result is NULL.
postgres=# select 1=3 OR null;
?column?
----------
(1 row)
- Both expressions are NULL so the result is NULL.
postgres=# select null OR null;
?column?
----------
(1 row)
NOT
NOT operator truth table
|
x1 |
NOT x1 |
|
TRUE |
FALSE |
|
FALSE |
TRUE |
|
NULL |
NULL |
Examples
- Expression is NOT true so the result is FALSE.
postgres=# select not true;
?column?
----------
f
(1 row)
- Expression is NOT false so the result is TRUE.
postgres=# select not false;
?column?
----------
t
(1 row)
- Expression is NOT NULL so the result is NULL.
postgres=# select not null;
?column?
----------
(1 row)
Using AND and OR operators together
We can combine the AND and OR operators in a SQL query to perform INSERT, UPDATE, SELECT, and DELETE operations.
Remember to include parentheses () so that the server knows in which order to evaluate the query.
Examples
- Both expressions return TRUE, so the result will be TRUE.
postgres=# select (5=5 and 5=5) and ( null or 4=4);
?column?
----------
t
(1 row)
- One expression returns TRUE and another returns NULL, so the result is NULL.
postgres=# select (5=5 and 5=5) and ( null and 4=4);
?column?
----------
(1 row)
Please refer to the PostgreSQL documentation for more details:
https://www.postgresql.org/docs/11/functions-logical.html.
Hope it helps!