Filtering rows with WHERE — Retrieving and Filtering Data

Objective

By the end of this lesson you can filter a table with WHERE using equality on numbers, text and boolean columns, and explain how PostgreSQL decides which rows to keep.

Why it matters: Real tables hold far more rows than any question needs: one customer's orders, this month's payments, products in one category. WHERE is how you get from the whole table to the rows that answer the question.

Dataset: Starter shop. Examples run in the editor on the right.

SELECT chooses columns. WHERE chooses rows. It goes after FROM and contains a condition: an expression that is either true or false for each row. PostgreSQL tests the condition on every row of the table, one row at a time, keeps the rows where it is true and drops the rest. Only then does it compute the SELECT list for the rows that survived.

SELECT column_a, column_b
FROM table_name
WHERE condition;

WHERE comes after FROM. The condition is checked once per row.

SELECT *
FROM orders
WHERE amount > 100;

id	city	status	amount
1	Riyadh	delivered	120
2	Jeddah	cancelled	90
3	Riyadh	delivered	60
4	Dammam	delivered	200
5	Jeddah	delivered	75
6	Riyadh	delivered	40
7	Dammam	delivered	30

PostgreSQL tests the condition on each row. Rows where it is true are kept (2 of 7); the others are dropped before anything else happens.

Pick a condition and watch which rows WHERE keeps (highlighted) and which it drops.

ExampleCustomers in Riyadh

SELECT customer_name, city
FROM customers
WHERE city = 'Riyadh';

For each of the 12 customers PostgreSQL asks: is city equal to 'Riyadh'? It is true for four of them, so the result has 4 rows. The others, including Reem whose city is NULL, are dropped.

Writing values correctly

Numbers are written as they are: WHERE customer_id = 1, WHERE price = 89.5. No quotes, no thousands separators.
Text goes in single quotes: 'Riyadh'. Double quotes mean a name (a column or alias), so WHERE city = "Riyadh" makes PostgreSQL look for a column called Riyadh and fail with column "Riyadh" does not exist. If the text itself contains an apostrophe, double it: 'Men''s Fashion'.
Text comparison is exact: 'riyadh' does not equal 'Riyadh', and 'Riyadh ' (with a trailing space) does not equal either.
Boolean columns already are true or false, so they can be the whole condition: WHERE discontinued keeps rows where it is true, WHERE NOT discontinued the others. WHERE discontinued = true also works.

ExampleOne customer's orders

SELECT order_id, order_date, product_id, status
FROM orders
WHERE customer_id = 1;

customer_id is a number, so no quotes are needed. Noura (customer 1) has three orders. Notice that customer_id is not in the SELECT list: WHERE can filter on any column of the table, whether or not you display it.

ExampleDiscontinued products at Wadi Market

SELECT sku, product_name, unit_price
FROM products
WHERE discontinued;

Wadi Market online store

discontinued is a boolean column, so it can be the condition on its own. Only the products no longer sold are returned, a handful out of 61.

ExampleExact means exact

SELECT customer_id, first_name, last_name, city
FROM customers
WHERE city = 'riyadh';

Wadi Market online store

Wadi Market has hundreds of customers in Riyadh, but this returns only the customer who typed the city in lowercase. Equality on text compares every character, including case and spaces. Keep this in mind: you'll fix it properly with lower() and trim() later in this track.

Quick check

What happens with WHERE status = "cancelled" in PostgreSQL?

Now practise

4 exercises on real data, checked in your browser (practice).