PostgreSQL WHERE

Introduction to PostgreSQL WHERE

The WHERE clause filters rows in SELECT, UPDATE, and DELETE statements. Only rows where the condition evaluates to TRUE are included (or affected).

SELECT select_list
FROM table_name
WHERE condition;

Comparison operators

-- Equal to
SELECT * FROM customer WHERE last_name = 'Smith';

-- Not equal
SELECT * FROM product WHERE price != 0;

-- Greater than / less than
SELECT * FROM orders WHERE total_amount > 100;

-- Range
SELECT * FROM orders WHERE placed_at >= '2024-01-01' AND placed_at < '2025-01-01';

BETWEEN

BETWEEN x AND y is inclusive on both ends (equivalent to >= x AND <= y):

SELECT * FROM payment WHERE amount BETWEEN 1.00 AND 5.00;

IN: matching a list of values

SELECT * FROM customer WHERE country IN ('US', 'CA', 'GB');

-- Exclusion
SELECT * FROM product WHERE category_id NOT IN (5, 10, 15);

LIKE: pattern matching

-- Starts with 'Ann'
SELECT * FROM customer WHERE first_name LIKE 'Ann%';

-- Contains 'gmail' (case-insensitive with ILIKE)
SELECT * FROM customer WHERE email ILIKE '%gmail%';

% matches any sequence of characters; _ matches exactly one character.

IS NULL / IS NOT NULL

-- Customers with no phone number on file
SELECT * FROM customer WHERE phone IS NULL;

-- Orders with a tracking number
SELECT * FROM orders WHERE tracking_number IS NOT NULL;

Combining conditions

SELECT *
FROM orders
WHERE status = 'shipped'
  AND placed_at >= now() - interval '7 days'
  AND total_amount > 50
ORDER BY placed_at DESC;

Subqueries in WHERE

-- Customers who placed at least one order over $500
SELECT * FROM customer
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE total_amount > 500
);

Production tips

• Index columns used in WHERE for equality and range queries. Unindexed filters on large tables cause full sequential scans.
• Be wary of NOT IN with a subquery that might return NULL — if any value in the list is NULL, NOT IN returns no rows. Use NOT EXISTS instead for nullable subqueries.
• Avoid wrapping indexed columns in functions (WHERE lower(email) = 'x' skips the index). Use a functional index or ILIKE instead.
• Test filter selectivity with EXPLAIN ANALYZE — the planner uses table statistics to estimate row counts.

Reference: PostgreSQL documentation — SELECT / WHERE.