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.
|!= or <>||Not Equal|
|>=||Greater than or equal|
|<=||Less than or equal|
|BETWEEN||Between some range|
|LIKE||Search for pattern|
|IN||Specify multiple possible values for a column|
|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
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
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);
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.