This PostgreSQL tutorial explains how to use the AND condition and the OR condition together in a PostgreSQL query with syntax and examples.
The PostgreSQL AND condition and OR condition can be combined in a SELECT, INSERT, UPDATE, or DELETE statement.
When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)
The syntax for the AND condition and OR condition together in PostgreSQL is:
Parameters or Arguments
condition1, condition2, condition_n are the conditions that are evaluated to determine if the records will be selected.
- The PostgreSQL AND & OR conditions allow you to test multiple conditions.
- Don't forget the order of operation parentheses!
Example - With SELECT Statement
Let's look at an example that combines the AND and OR conditions in a SELECT statement.
WHERE (city = 'Miami' AND first_name = 'Sarah')
OR (employee_id <= 2000);
This AND & OR example would return all employees that live in Miami whose first_name is 'Sarah' and all employees whose employee_id is less than or equal to 2000. 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!
The next example takes a look at a more complex statement.
SELECT employee_id, last_name, first_name
WHERE (last_name = 'Smith')
OR (last_name = 'Anderson' AND state = 'Florida')
OR (last_name = 'Ferguson' AND status = 'Active' AND state = 'Calfornia');
This AND & OR example would return all employee_id, last_name, and first_name values from the employees table whose the last_name is 'Smith' OR whose last_name is 'Anderson' and the state is 'Florida' OR whose last_name is 'Ferguson', the status is 'Active' and the state is 'California'.
Example - With INSERT Statement
This next AND & OR example demonstrates how the AND condition and OR condition can be combined in the INSERT statement.
INSERT INTO contacts
SELECT customer_id, customer_name
WHERE (last_name = 'Anderson' OR last_name = 'Smith')
AND customer_id > 340;
This PostgreSQL AND and OR example would insert into the contacts table, all customer_id and customer_name records from the customers table whose last_name is either 'Anderson' or 'Smith' and the customer_id is greater than 340.
Example - With UPDATE Statement
This AND & OR example shows how the AND and OR conditions can be used in the UPDATE statement.
SET status = 'Active'
WHERE (first_name = 'Jane' or last_name = 'Doe')
AND employee_id > 1000;
This PostgreSQL AND & OR condition example would update all status values to 'Active' in the employees table where the either the first_name is 'Jane' or last_name is 'Doe' and the employee_id is greater than 1000.
Example - With DELETE Statement
Finally, this last AND & OR example demonstrates how the AND and OR conditions can be used in the DELETE statement.
DELETE FROM employees
WHERE employee_id >= 500
AND (last_name = 'Smith' OR last_name = 'Johnson');
This PostgreSQL AND and OR condition example would delete all records from the employees table where the employee_id is greater than or equal to 500 and last_name was either 'Smith' or 'Anderson'.