WHERE: Filtering Rows

WHERE filters the rows returned by a query. Only rows where the condition evaluates to TRUE are included in the result.

Prior lesson

This lesson builds on SELECT and FROM. The sample dataset is defined in the Introduction to SQL lesson.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Supported comparison operators:

Operator	Meaning
=	Equal to
<>	Not equal to
!=	Not equal to (same as <>, both are accepted)
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to

Examples

Example 1: Filter by a number

SELECT
    order_id,
    customer_id,
    total_amount
FROM orders
WHERE total_amount > 200;

Result:

order_id	customer_id	total_amount
1	1	999.00
3	2	349.00
4	3	796.00
6	1	349.00
7	2	999.00
9	3	999.00

Example 2: Filter by a string value

String values must be wrapped in single quotes.

SELECT
    order_id,
    order_date,
    total_amount,
    status
FROM orders
WHERE status = 'completed';

Result:

order_id	order_date	total_amount	status
1	2024-01-05	999.00	completed
2	2024-01-10	59.98	completed
3	2024-01-15	349.00	completed
4	2024-02-01	796.00	completed
7	2024-03-15	999.00	completed
8	2024-04-01	89.97	completed

Example 3: Filter by a date

SELECT
    order_id,
    order_date,
    total_amount
FROM orders
WHERE order_date >= '2024-03-01';

Result:

order_id	order_date	total_amount
6	2024-03-01	349.00
7	2024-03-15	999.00
8	2024-04-01	89.97
9	2024-04-10	999.00
10	2024-05-01	24.95

Common Mistake

Cannot reference SELECT aliases in WHERE

The WHERE clause is evaluated before SELECT, so column aliases defined in SELECT are not available yet.

-- This fails
SELECT total_amount * 0.9 AS discounted_price
FROM orders
WHERE discounted_price < 100;  -- ERROR: column "discounted_price" does not exist

-- This works
SELECT total_amount * 0.9 AS discounted_price
FROM orders
WHERE total_amount * 0.9 < 100;

Practice

Write a query that returns all columns from orders where the status is 'pending'.

Show answer

SELECT *
FROM orders
WHERE status = 'pending';

Expected result:

order_id	customer_id	product_id	order_date	quantity	total_amount	status
9	3	1	2024-04-10	1	999.00	pending
10	4	5	2024-05-01	5	24.95	pending