PostgreSQL
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.
The WHERE syntax with select query.
SELECT * FROM table_name WHERE condition;
The condition is used to filter the rows.
condition
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
comments
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)
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)
We can use the following operators with WHERE clause.
Equal to
Not equal to
Less than
Greater than
Less than or equal to
Greater than or equal to
IN
LIKE
BETWEEN
Let us look at some examples.
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".
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)
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)
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 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)
We use the LIKE operator to match pattern to select rows from the tables.
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)
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)
In the following example we are selecting all the comments having the pattern 'co' in the commentBody column.
commentBody
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)
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)