PostgreSQL WHERE: filtering rows

WHERE keeps only the rows for which a condition is true. Conditions use comparison operators (=, <> or !=, <, >, <=, >=) and combine with AND, OR and NOT. AND binds tighter than OR, so use parentheses when mixing them.

Useful shortcuts: IN (...) matches any value in a list; BETWEEN a AND b is inclusive on both ends; LIKE matches patterns where % means “any characters” and _ means “exactly one character”; ILIKE is PostgreSQL’s case-insensitive LIKE.

NULL means “unknown”, so city = NULL is never true (it evaluates to NULL). Always test with IS NULL / IS NOT NULL. IS DISTINCT FROM compares values while treating NULLs as equal to each other.

Syntax

SELECT columns
FROM table
WHERE condition [AND | OR condition ...];

Examples

Edit the code and run it. Output is printed the way psql prints it.

4.1Simple comparison

1
2
3

SELECT name, price
FROM products
WHERE price > 100;

Each row is tested with price > 100; only those that pass are returned.

4.2AND, OR and parentheses

SELECT name, category, price, stock
FROM products
WHERE (category = 'Audio' OR category = 'Electronics')
  AND stock > 0
  AND price < 200;

Without the parentheses, AND would be applied first and Electronics products would skip the stock and price checks. Text comparisons are case-sensitive: 'audio' would match nothing.

4.3IN, BETWEEN and NOT

SELECT name, country, signup_date
FROM customers
WHERE country IN ('India', 'UK')
  AND signup_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND NOT tier = 'free';

IN is a shorter way of writing several ORs.
BETWEEN includes both boundary dates.
The date strings are converted to date automatically.

4.4Pattern matching with LIKE and ILIKE

1
2
3

SELECT name FROM products WHERE name LIKE '%Mouse%';
SELECT name FROM products WHERE name ILIKE 'm%';
SELECT email FROM customers WHERE email LIKE '%.co.uk';

'%Mouse%': contains “Mouse”. ILIKE 'm%': starts with m or M. '%.co.uk': ends with “.co.uk”.

4.5Working with NULL

1
2
3

SELECT name, city FROM customers WHERE city IS NULL;
SELECT first_name, dept_id FROM employees WHERE dept_id IS NULL;
SELECT count(*) AS rows_matching_equals_null FROM customers WHERE city = NULL;

The third query returns 0 because city = NULL is never true. This is one of the most common SQL mistakes.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 4.1

Find the name and price of products in the Audio category that cost less than 150.

Hint

Two conditions joined with AND.

Solution

SELECT name, price FROM products WHERE category = 'Audio' AND price < 150;

Exercise 4.2

List the first_name and last_name of employees who earn between 60,000 and 100,000 (inclusive) and are active.

Hint

BETWEEN plus is_active = true (or just is_active).

Solution

SELECT first_name, last_name FROM employees WHERE salary BETWEEN 60000 AND 100000 AND is_active;

Exercise 4.3

Show the name of customers whose email does not end with .com.

Hint

NOT LIKE

Solution

SELECT name FROM customers WHERE email NOT LIKE '%.com';