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 do not need.

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

Syntax

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

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

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

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

exampledb# 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)

In the following examples, we use logical operators to read data from the table.

AND

FindAGE (age)field greater than or equal to 25, andSALARY (salary)field greater than or equal to 65000:

exampledb=# 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

FindAGE (age)field greater than or equal to 25, orSALARY (salary)field greater than or equal to 65000:

exampledb=# 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 theAGE (age)field is not empty:

exampledb=#  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

Find in the COMPANY tableNAME (name)field starts with Pa:

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

IN

The following SELECT statement lists theAGE (age)field equal to 25 or 27:

exampledb=# 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 theAGE (age)field not equal to 25 or 27:

exampledb=# 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 theAGE (age)field between 25 and 27:

exampledb=# 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 an SQL subquery. The subquery readsSALARY (salary)data with field greater than 65000, and then through theEXISTSoperator to determine whether it returns rows. If rows are returned, it reads all theAGE (age)fields.

exampledb=# 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 an SQL subquery. The subquery readsSALARY (salary)field greater than 65000 in theAGE (age)field data, and then uses the>operator to query data greater than theAGE (age)field data:

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

Other Extensions

PostgreSQL Advanced