PostgreSQL - AND & OR Clauses

PostgreSQL: Combining Conditions with AND, OR, and NOT

In real-world queries, you often need to filter data based on multiple criteria. PostgreSQL provides the AND, OR, and NOT logical operators to build powerful and precise WHERE clauses.

Setup: Sample ‘employees’ Table

For our examples, we will use a more realistic employees table. You can create and populate it with this script:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date DATE
);

INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
('Alice', 'Smith', 'Engineering', 92000.00, '2022-03-15'),
('Bob', 'Johnson', 'Engineering', 115000.00, '2021-07-01'),
('Charlie', 'Brown', 'Sales', 85000.00, '2023-01-20'),
('Diana', 'Prince', 'Sales', 125000.00, '2020-11-10'),
('Eve', 'Adams', 'HR', 78000.00, '2022-09-01');

The AND Operator

The AND operator returns a row only if all conditions connected by AND are true. It’s used to narrow your search results.

Syntax

SELECT column_list
FROM table_name
WHERE [condition1] AND [condition2] AND ... [conditionN];

Example: Find High-Earning Engineers

Let’s find all employees in the ‘Engineering’ department with a salary greater than $100,000.

SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering' AND salary > 100000.00;

This query will return:

 first_name | last_name |   salary
------------+-----------+------------
 Bob        | Johnson   | 115000.00
(1 row)

The OR Operator

The OR operator returns a row if at least one of the conditions connected by OR is true. It’s used to broaden your search results.

Syntax

SELECT column_list
FROM table_name
WHERE [condition1] OR [condition2] OR ... [conditionN];

Example: Find HR staff or anyone hired in 2023

Let’s find all employees who are either in the ‘HR’ department or were hired in the year 2023.

SELECT first_name, department, hire_date
FROM employees
WHERE department = 'HR' OR hire_date >= '2023-01-01';

This query will return:

 first_name | department | hire_date
------------+------------+------------
 Charlie    | Sales      | 2023-01-20
 Eve        | HR         | 2022-09-01
(2 rows)

Combining AND and OR: Operator Precedence

A very common source of errors is mixing AND and OR without understanding precedence. PostgreSQL evaluates AND operators before OR operators. To ensure your logic is correct, always use parentheses ().

Example: Find Sales staff earning over $100k, OR any Engineer

Let’s find employees who are (in the ‘Sales’ department AND have a salary over $100,000) OR (are in the ‘Engineering’ department).

SELECT first_name, department, salary
FROM employees
WHERE (department = 'Sales' AND salary > 100000.00)
   OR (department = 'Engineering');

This query correctly returns:

 first_name | department  |   salary
------------+-------------+------------
 Alice      | Engineering |  92000.00
 Bob        | Engineering | 115000.00
 Diana      | Sales       | 125000.00
(3 rows)

Debugging Tip: What happens without parentheses?

If we wrote the query without parentheses, department = 'Sales' AND salary > 100000.00 would be evaluated first, yielding an incorrect result for our goal.

Best Practices and Common Pitfalls

• Always Use Parentheses: When mixing AND and OR, use parentheses to make your query’s logic unambiguous and easy to read.
• Handling NULLs: Logical operators involving NULL can be tricky. For example, NULL AND TRUE results in NULL, not FALSE. A WHERE clause only includes rows that evaluate to TRUE. Rows evaluating to FALSE or NULL are excluded.
• Readability: For very complex conditions, format your WHERE clause with line breaks and indentation to improve readability for you and your teammates.