PostgreSQL - Select using WHERE clause - PostgreSQL - DYclassroom

In this tutorial we are going to fetch data from the tables using WHERE clause in PostgreSQL.

Do check the previous tutorial SELECT FROM Table. In that tutorial we have covered how to fetch rows from the table using the SELECT query.

The WHERE clause is used with the SELECT query to fetch rows from the tables based on some conditions.

WHERE syntax

The WHERE syntax with select query.

SELECT * FROM table_name
WHERE condition;

The condition is used to filter the rows.

We will use employee and comments tables that we have been using in this tutorial series. Check PostgreSQL - CREATE Table tutorial to create those tables.

Employee Table

The employee table will have the following entries.

SELECT * FROM employee;

 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
 e04        | Tin       | Tin      |     7 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
 e05        | Bob       | Coder    |     5 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(5 rows)

Comments Table

Following are the rows in the comments table.

SELECT * FROM comments;

 commentId |     commentBody     |   lastModifiedAt    |      createdAt      | employeeId
-----------+---------------------+---------------------+---------------------+------------
         1 | Awesome             | 2021-01-01 02:03:04 | 2021-01-01 02:03:04 | e03
         2 | Hello World         | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 | e03
         3 | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 | e01
         4 | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 | e04
         5 | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 | e01
(5 rows)

Operators

We can use the following operators with WHERE clause.

Operator	Example	Description
=	leftSide = rightSide	We use the `Equal to` operator to check if the left side is equal to the right side.
!=	leftSide != rightSide	We use the `Not equal to` operator to check if the left side is not equal to the right side.
<	leftSide < rightSide	We use the `Less than` operator to check if the left side is less than the right side.
>	leftSide > rightSide	We use the `Greater than` operator to check if the left side is greater than the right side.
<=	leftSide <= rightSide	We use the `Less than or equal to` operator to check if the left side is less than or equal to the right side.
>=	leftSide >= rightSide	We use the `Greater than or equal to` operator to check if the left side is greater than or equal to the right side.
IN	leftSide IN ('val1', 'val2', ...)	We use the `IN` to check if left side value is present in possible list of values.
LIKE	leftSide LIKE pattern	We use the `LIKE` operator to search for matching pattern.
BETWEEN	leftSide BETWEEN option1 AND option2	We use the `BETWEEN` operator to check if left side value is in between option1 and option2, both inclusive.

Let us look at some examples.

The = and != operator

In the following example we are going to fetch the detail of employee with id e01 using the = operator.

SELECT * FROM employee WHERE "employeeId" = 'e01';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |       email
------------+-----------+----------+-------+---------------------+---------------------+------------+-------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
(1 row)

We use double quotes to enclose column names that are in camel-case.

In the above example we are enclosing the column employeeId with double quotes as "employeeId".

Double quotes is not required if column name is all in lower-case.

Similary, we use the not equal to != operator to select all the rows that does not match the expressed value.

In the following example we are selecting all the employees whose employeeId is not equal to e01.

SELECT * FROM employee WHERE "employeeId" != 'e01';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
 e04        | Tin       | Tin      |     7 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
 e05        | Bob       | Coder    |     5 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(4 rows)

The > and < operator

In the following example we are going to fetch all the employees with score greater than 5.

SELECT * FROM employee WHERE score > 5;


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
 e04        | Tin       | Tin      |     7 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
(3 rows)

In the following example we are going to fetch all the employees with score less than 6.

SELECT * FROM employee WHERE score < 6;


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e05        | Bob       | Coder    |     5 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(2 rows)

The <= and >= operators

In the following example we are fetching all the employees born on or before '1900-05-01'.

SELECT * FROM employee WHERE birthday <= '1900-05-01';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
(2 rows)

In the following example we are fetching all the employees born on or after '1900-05-01'.

SELECT * FROM employee WHERE birthday >= '1900-05-01';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
 e04        | Tin       | Tin      |     7 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
 e05        | Bob       | Coder    |     5 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(3 rows)

IN operator

In the following example we are selecting details of employeeid 'e01', 'e03' and 'e05'.

SELECT * FROM employee WHERE "employeeId" IN ('e01', 'e03', 'e05');


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
 e05        | Bob       | Coder    |     5 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(3 rows)

LIKE operator

We use the LIKE operator to match pattern to select rows from the tables.

Match pattern at the start of a value.
Match pattern at the end of a value.
Match pattern in between a value.

LIKE 'pattern%' - match starting

In the following example we are selecting all the employees having firstName starting with 'J'.

SELECT * FROM employee WHERE "firstName" LIKE 'J%';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(2 rows)

LIKE '%pattern' - match ending

In the following example we are selecting all the employees having lastName ending with 'oe'.

SELECT * FROM employee WHERE "lastName" LIKE '%oe';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(2 rows)

LIKE '%pattern%' - match in between

In the following example we are selecting all the comments having the pattern 'co' in the commentBody column.

SELECT * FROM comments WHERE "commentBody" LIKE '%co%';


 commentId |     commentBody     |   lastModifiedAt    |      createdAt      | employeeId
-----------+---------------------+---------------------+---------------------+------------
         4 | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 | e04
         5 | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 | e01
(2 rows)

BETWEEN operator

In the following example we are selecting all the employees born from '1900-03-01' to '1900-06-01' both inclusive.

SELECT * FROM employee WHERE birthday BETWEEN '1900-03-01' AND '1900-06-01';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(1 row)

PostgreSQL - Select using WHERE clause