PostgreSQL
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.
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.
condition1
condition2
Now lets see an example.
Fetch employees satisfying ALL the given conditions.
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)
We use the OR operator when we want to filter rows based on any one matching filter condition in the WHERE clause.
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.
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)
We use the NOT operator to fetch rows only if the filter condition in the WHERE clause is not satisfied.
SELECT * FROM table_name WHERE NOT condition;
For the above query we will fetch only those rows for which condition is FALSE.
condition
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)