PostgreSQL - WHERE clause with AND, OR and NOT operators

In this tutorial we will learn to select rows from tables using WHERE clause along with AND, OR and NOT operators in PostgreSQL.

To learn more about SELECT query check out the following tutorial PostgreSQL - SELECT FROM Table.

Select using WHERE with AND operator

We use the AND operator when we want to use get rows only if all the filtering conditions in the WHERE clause is satisfied.

Syntax:

SELECT * FROM table_name WHERE condition1 AND condition2;

In the above example we will be fetching only those rows for which condition1 and condition2 both are TRUE.

Now lets see an example.

Fetch employees satisfying ALL the given conditions.

• Employee with score greater than 5
• Employee born on or after '1900-05-01'

SELECT * FROM employee WHERE score > 5 AND 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
(2 rows)

Select using WHERE with OR operator

We use the OR operator when we want to filter rows based on any one matching filter condition in the WHERE clause.

Syntax:

SELECT * FROM table_name  WHERE condition1 OR condition2;

For the above query we will fetch all those rows for which either condition1 or condition2 is TRUE.

Now let us see an OR example.

Fetch employees satisfying any one of the given conditions.

• Employee with score greater than 5
• Employee born on or after '1900-05-01'

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


 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)

Select using WHERE with NOT operator

We use the NOT operator to fetch rows only if the filter condition in the WHERE clause is not satisfied.

Syntax:

SELECT * FROM table_name WHERE NOT condition;

For the above query we will fetch only those rows for which condition is FALSE.

Let us see an example of NOT operator.

Fetch employees NOT having lastName as 'Doe'.

SELECT * FROM employee WHERE NOT "lastName" = 'Doe';


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