PostgreSQL - WHERE Clause

PostgreSQL - The WHERE Clause

The WHERE clause is one of the most fundamental components of SQL. It is used to filter records and retrieve only the rows that satisfy a specific condition. It can be used with SELECT, UPDATE, and DELETE statements.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE [condition];

The [condition] is an expression that evaluates to true, false, or unknown. It typically involves comparison operators (=, >, <, <>, !=), logical operators (AND, OR, NOT), and other specialized operators.

Practical Examples

Let’s set up an employees table for our examples:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    department TEXT,
    salary NUMERIC(10, 2)
);

INSERT INTO employees (name, age, department, salary) VALUES
('Alice', 32, 'Engineering', 95000.00),
('Bob', 25, 'Marketing', 60000.00),
('Charlie', 28, 'Engineering', 80000.00),
('David', 45, 'Sales', 120000.00),
('Eve', 32, 'Marketing', 68000.00),
('Frank', 38, 'Sales', 115000.00);

Using Logical Operators (AND, OR)

Find all employees in the ‘Engineering’ department who are older than 25:

SELECT name, department, age
FROM employees
WHERE department = 'Engineering' AND age > 25;

Find all employees who are either in the ‘Sales’ department or have a salary over $100,000:

SELECT name, department, salary
FROM employees
WHERE department = 'Sales' OR salary > 100000;

Using IN and NOT IN

Find employees in the ‘Marketing’ or ‘Sales’ departments:

SELECT name, department
FROM employees
WHERE department IN ('Marketing', 'Sales');

Using BETWEEN

Find employees with a salary between $70,000 and $100,000 (inclusive):

SELECT name, salary
FROM employees
WHERE salary BETWEEN 70000 AND 100000;

Pattern Matching with LIKE and ILIKE

Find employees whose name starts with ‘A’. LIKE is case-sensitive.

SELECT name FROM employees WHERE name LIKE 'A%';

PostgreSQL offers ILIKE for case-insensitive matching. Find employees whose department contains ‘market’, regardless of case:

SELECT name, department FROM employees WHERE department ILIKE '%market%';

Checking for NULL Values

First, let’s add an employee with a missing department:

INSERT INTO employees (name, age, salary) VALUES ('Grace', 29, 72000);

Find employees who are not assigned to a department. Always use IS NULL or IS NOT NULL, not = NULL.

SELECT name, department FROM employees WHERE department IS NULL;

Using Subqueries with EXISTS

The EXISTS operator checks for the existence of rows in a subquery. It’s an efficient way to check for a condition without retrieving the actual data. Let’s find all employees who work in a department that has at least one person earning over $100,000.

SELECT name, department
FROM employees e1
WHERE EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.department = e1.department
    AND e2.salary > 100000
);

This query returns all employees from the ‘Sales’ department, because that department contains high earners (‘David’ and ‘Frank’). This is a powerful pattern for complex conditional filtering.