PostgreSQL: Combining AND and OR Conditions in WHERE Clauses

Master the use of `AND` and `OR` operators in PostgreSQL to create complex `WHERE` clauses and effectively filter data.

Combining `AND` and `OR` Conditions in PostgreSQL

Understanding `AND` and `OR`

In PostgreSQL (and most SQL databases), `AND` and `OR` are logical operators used in `WHERE` clauses to combine multiple conditions. They control how conditions are evaluated when filtering data. A row is selected only if all conditions linked by `AND` are true. A row is selected if at least one condition linked by `OR` is true. Parentheses are crucial for specifying the order of operations when using both `AND` and `OR` in a single statement.

`AND` and `OR` Syntax

The basic syntax for combining `AND` and `OR` conditions is:

SELECT column1, column2, ... FROM table_name WHERE (condition1 AND condition2 ...) OR (condition3 OR condition4 ...);

Parentheses are used to group conditions; the order of operations follows standard mathematical precedence (parentheses first, then `AND`, then `OR`).

Examples: Using `AND` and `OR`

These examples demonstrate using `AND` and `OR` in various SQL statements. Assume you have sample tables already populated with data. The output will reflect the data in your tables. Pay close attention to how the parentheses control which conditions are grouped together.

Example 1: `AND` and `OR` with `INSERT`

This example inserts rows into the `department` table based on conditions from the `employee` table. It illustrates inserting data based on multiple combined conditions (`OR` within the parentheses, then `AND`).

INSERT INTO department (phone, address)
SELECT phone, address
FROM employee
WHERE (employee_name = 'Nia Davis' OR employee_name = 'Kat Taylor')
  AND emp_id < 5;

Example 2: `AND` and `OR` with `SELECT`

This example selects customers based on multiple conditions, using parentheses to control the order of operations.

SELECT cust_id, cust_name, cust_address, cust_age
FROM customer
WHERE (cust_address = 'Florida' AND cust_name = 'Harvey')
   OR cust_age >= 26;

Example 3: `AND` and `OR` with `SELECT` (More Complex)

This demonstrates a more complex `SELECT` query with multiple `AND` and `OR` conditions.

SELECT car_id, car_name, car_model, car_price, car_color, body_style
FROM car
WHERE (body_style = 'coupe')
   OR (body_style = 'wagon' AND car_color = 'blue')
   OR (body_style = 'sedan' AND car_color = 'black' AND car_price = 63890);

Example 4: `AND` and `OR` with `UPDATE`

This example updates rows in the `department` table based on conditions. The example shows that `AND` and `OR` conditions can be used within an `UPDATE` statement to modify only those rows satisfying certain criteria. The parentheses dictate the order of evaluation.

UPDATE department
SET dept_name = 'RESEARCH'
WHERE (emp_fname = 'Flora' OR dept_id = 4)
  AND emp_id < 5;

Example 5: `AND` and `OR` with `DELETE`

This example shows how `AND` and `OR` are used within a `DELETE` statement.

DELETE FROM customer
WHERE (cust_address = 'Florida' AND cust_name = 'Harvey')
   OR cust_age >= 26;

Conclusion

The `AND` and `OR` operators in PostgreSQL are fundamental for creating flexible and targeted queries. Careful use of parentheses to control the order of operations is crucial for obtaining the correct results. Mastering these operators allows for powerful data filtering and manipulation.