This PostgreSQL tutorial explains how to use the PostgreSQL WHERE clause with syntax and examples.
The PostgreSQL WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.
The syntax for the WHERE clause in PostgreSQL is:
Parameters or Arguments
The conditions that must be met for records to be selected.
Example - With Single condition
It is difficult to explain the syntax for the PostgreSQL WHERE clause, so let's look at some examples.
WHERE first_name = 'Sarah';
In this PostgreSQL WHERE clause example, we've used the WHERE clause to filter our results from the employees table. The SELECT statement above would return all rows from the employees table where the first_name is 'Sarah'. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.
Example - Using AND condition
WHERE last_name = 'Anderson'
AND employee_id <= 400;
This PostgreSQL WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND condition to return all employees whose last_name is 'Anderson' and whose employee_id is less than or equal to 400.
Example - Using OR condition
WHERE product_type = 'Hardware'
OR product_type = 'Software';
This PostgreSQL WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition. In this case, this SELECT statement would return all product_name values where the product_type is 'Hardware' or 'Software'.
Example - Combining AND & OR conditions
WHERE (last_name = 'Anderson' AND first_name = 'John')
OR (employee_id >= 699);
This PostgreSQL WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND condition and the OR condition. This example would return all employees whose last_name is 'Anderson and first_name is 'John' as well as all employees whose employee_id is greater than or equal to 699.
The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!
Example - Joining Tables
SELECT products.product_name, inventory.quantity
INNER JOIN inventory
ON products.product_id = inventory.product_id
WHERE products.product_type = 'Hardware';
This PostgreSQL WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all product_name and quantity values where there is a matching record in the products and inventory tables based on product_id, and where the product_type is 'Hardware'.