PostgreSQL WHERE Clause: Filter Data

The WHERE clause is used to filter the rows in the result. It can be used with SELECT, UPDATE, and DELETE statements.

Syntax: 

SELECT <column_list>
FROM <table_name>
[ WHERE <conditions>]
[ GROUP BY ]
[ HAVING condition ]
[ ORDER BY ]

        

The WHERE clause is specified just after the FROM clause and before the GROUP BY, HAVING and ORDER BY clauses. After the WHERE keyword, you can specify one or more conditions.

The WHERE conditions must evaluate to be true or false to filter the rows. Any row that does not satisfy this condition will be eliminated from the result.

In PostgreSQL, the FROM clause is evaluated first, then the WHERE clause, and then the SELECT clause.

The WHERE condition must use the following one or more comparison and Logical operators.

Operator	Description
=	Equal
!= or <>	Not Equal
>	Greater than
>=	Greater than or equal
<	Less than
<=	Less than or equal
BETWEEN	Between some range
LIKE	Search for pattern
IN	Specify multiple possible values for a column

Operator	Description
AND	Logical operator AND
OR	Logical operator OR
NOT	Negate the result of the operator

Let's see how to use the WHERE clause in the SELECT statement to fetch the data from the following employee table.

The following example filters data using the WHERE clause with the comparison operator equal to =.

Example: WHERE Clause with EqualTo Operator

SELECT *
FROM employee 
WHERE first_name = 'Charlton';

The above SELECT statement retrieves employees whose first_name is Charlton. It used the logical operator = with the WHERE clause WHERE first_name = 'Charlton'. The following shows the result of the above query in pgAdmin.

The following example uses the greater than operator > to retrieve rows where the value in the salary column is greater than 50000.

Example: WHERE Clause with GreaterThan Operator

SELECT * FROM employee
WHERE salary > 50000;

The following shows the result in pgAdmin.

You can use the WHERE clause with the IN operator. The IN operator is used to define a list of values to search for. The following query will return the list of employees whose emp_id = 1 OR emp_id = 6.

Example: WHERE Clause with IN Operator

SELECT * FROM employee
WHERE emp_id IN (1,6);

Multiple conditions

Use the AND and OR operators to specify multiple conditions with the WHERE clause.

The following query will get the list of employees with Gender = F AND Salary > 50000. It will retrieve rows that satisfies both conditions.

Example: Multiple Conditions using AND

SELECT * FROM employee
WHERE gender = 'F' AND salary > 50000;

The following query uses the OR operator to find a list of employees whose gender is Female or salary is greater than 50000. It will retrieve rows that satisfies at least one of the conditions.

Example: Multiple Conditions using OR

SELECT * FROM employee
WHERE gender = 'F' OR salary > 50000;

Thus, you can use the WHERE clause to filter the data.