How to use logical operators in PostgreSQL

 

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!