PostgreSQL WHERE Clause

In PostgreSQL, when we need to query data from a single table or multiple tables based on specified conditions, we can add the WHERE clause to the SELECT statement to filter out the data we don't need.

The WHERE clause can not only be used in SELECT statements but also in UPDATE, DELETE, and other statements.

Syntax

The following is the general syntax for using the WHERE clause in a SELECT statement to read data from the database:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]

We can use comparison operators or logical operators such as >, <, =, LIKE, NOT, etc., in the WHERE clause.

Create a COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

AND

Find data where the AGE field is greater than or equal to 25, and the SALARY field is greater than or equal to 65000:

tutorialprodb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

OR

Find data where the AGE field is greater than or equal to 25, or the SALARY field is greater than or equal to 65000:

tutorialprodb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(4 rows)

NOT NULL

Find records in the COMPANY table where the AGE field is not null:

tutorialprodb=#  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
  id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   1 | Paul  |  32 | California |  20000
   2 | Allen |  25 | Texas      |  15000
   3 | Teddy |  23 | Norway     |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   5 | David |  27 | Texas      |  85000
   6 | Kim   |  22 | South-Hall |  45000
   7 | James |  24 | Houston    |  10000
(7 rows)

LIKE

The following query retrieves data from the NAME field in the COMPANY table where the name starts with "Pa":

tutorialprodb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
id | name | age | address    | salary
----+------+-----+-----------+--------
  1 | Paul |  32 | California |  20000

IN

The following SELECT statement lists data where the AGE field is either 25 or 27:

tutorialprodb=# SELECT * FROM COMPANY WHERE AGE IN (25, 27);
 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

NOT IN

The following SELECT statement lists data where the AGE field is neither 25 nor 27:

tutorialprodb=# SELECT * FROM COMPANY WHERE AGE NOT IN (25, 27);
 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(4 rows)

BETWEEN

The following SELECT statement lists data where the AGE field is between 25 and 27:

tutorialprodb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

Subquery

The following SELECT statement uses a subquery in SQL. The subquery retrieves data where the SALARY field is greater than 65000, and then uses the EXISTS operator to check if it returns any rows. If it does, it retrieves all AGE fields:

tutorialprodb=# SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
 age
-----
  32
  25
  23
  25
  27
  22
  24
(7 rows)

The following SELECT statement also uses a subquery in SQL. The subquery retrieves AGE fields where the SALARY field is greater than 65000, and then uses the > operator to query for AGE fields greater than those retrieved:

tutorialprodb=# SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
 id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000