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.
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 =.
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.
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.
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.
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.
SELECT * FROM employee
WHERE gender = 'F' OR salary > 50000;
Thus, you can use the WHERE clause to filter the data.