PostgreSQL WHERE Clause

The WHERE clause filters rows returned by a query. Without it, SELECT returns every row in a table. With WHERE, only rows that meet a specified condition are included in the result. This is fundamental to working with real data, where only a subset of records is typically needed at any time.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

The condition is an expression that evaluates to either true or false for each row. Rows where the condition is true are included; rows where it is false are excluded.

Comparison Operators

The most common way to write a condition is using a comparison operator between a column and a value.

Example

SELECT name, age FROM students WHERE age = 22;

This returns only the students whose age is exactly 22.

Filtering Text Values

Text comparisons use single quotes around the value:

SELECT * FROM employees WHERE department = 'IT';

Text comparisons in PostgreSQL are case-sensitive by default. 'IT' and 'it' are treated as different values. To perform a case-insensitive comparison, use the ILIKE operator or convert both sides to the same case using LOWER().

Combining Conditions with AND and OR

AND Operator

Both conditions must be true for a row to be included:

SELECT * FROM employees
WHERE department = 'IT' AND salary > 60000;

This returns IT employees who also earn more than 60,000.

OR Operator

At least one condition must be true:

SELECT * FROM employees
WHERE department = 'IT' OR department = 'Finance';

This returns employees from either the IT or Finance department.

Combining AND and OR

When mixing AND and OR, use parentheses to control evaluation order. Without parentheses, AND is evaluated before OR:

SELECT * FROM employees
WHERE (department = 'IT' OR department = 'Finance')
AND salary > 65000;

This returns employees from IT or Finance who earn more than 65,000.

The NOT Operator

NOT reverses a condition:

SELECT * FROM employees WHERE NOT department = 'HR';

This returns all employees except those in HR. The same result can be achieved using <>:

SELECT * FROM employees WHERE department <> 'HR';

The BETWEEN Operator

BETWEEN filters rows within a range, inclusive of both boundary values:

SELECT name, salary FROM employees
WHERE salary BETWEEN 50000 AND 75000;

This is equivalent to writing salary >= 50000 AND salary <= 75000, but cleaner to read.

The IN Operator

IN checks whether a value matches any item in a list:

SELECT * FROM employees
WHERE department IN ('IT', 'Finance', 'Marketing');

This is more concise than writing multiple OR conditions. NOT IN excludes the listed values:

SELECT * FROM employees
WHERE department NOT IN ('HR', 'Admin');

The LIKE Operator (Pattern Matching)

LIKE searches for a pattern within a text column. Two wildcards are available:

• % — matches any sequence of zero or more characters
• _ — matches exactly one character

-- Names that start with 'A'
SELECT name FROM students WHERE name LIKE 'A%';

-- Names that end with 'n'
SELECT name FROM students WHERE name LIKE '%n';

-- Names with exactly 4 characters
SELECT name FROM students WHERE name LIKE '____';

-- Names containing 'ar' anywhere
SELECT name FROM students WHERE name LIKE '%ar%';

Use ILIKE instead of LIKE for case-insensitive matching:

SELECT name FROM students WHERE name ILIKE 'a%';

This returns names starting with either 'A' or 'a'.

Checking for NULL Values

NULL represents a missing or unknown value. Comparing NULL with = does not work as expected in SQL:

-- This does NOT work correctly
SELECT * FROM employees WHERE phone = NULL;

-- Use IS NULL instead
SELECT * FROM employees WHERE phone IS NULL;

-- Use IS NOT NULL to find rows where the value exists
SELECT * FROM employees WHERE phone IS NOT NULL;

NULL is never equal to anything, including itself. Always use IS NULL or IS NOT NULL when working with missing values.

Example: Filtering a Real Dataset

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC(8, 2),
    stock INT
);

INSERT INTO products (name, category, price, stock)
VALUES
    ('Laptop', 'Electronics', 1200.00, 15),
    ('Phone', 'Electronics', 800.00, 30),
    ('Desk', 'Furniture', 350.00, 8),
    ('Chair', 'Furniture', 150.00, 20),
    ('Notebook', 'Stationery', 5.00, 200),
    ('Pen', 'Stationery', 1.50, NULL);

-- Electronics priced above 500
SELECT name, price FROM products
WHERE category = 'Electronics' AND price > 500;

-- Products with low or unknown stock
SELECT name, stock FROM products
WHERE stock < 10 OR stock IS NULL;

-- Products not in Stationery
SELECT name, category FROM products
WHERE category NOT IN ('Stationery');

-- Products with names starting with 'P'
SELECT name FROM products WHERE name LIKE 'P%';

Key Points

• WHERE filters rows based on conditions that evaluate to true or false.
• Comparison operators include =, <>, >, <, >=, and <=.
• AND requires all conditions to be true; OR requires at least one.
• BETWEEN checks for values within a range (inclusive).
• IN checks whether a value matches any item in a list.
• LIKE uses % and _ wildcards for pattern matching; ILIKE is the case-insensitive version.
• NULL comparisons always use IS NULL or IS NOT NULL, never =.