CodeStride
Log inStart free
Getting Started

Filtering rows with WHERE in SQL

Topic 3 of 45 · 9 minFree lesson

Tables can hold millions of rows, and you rarely want them all. WHERE keeps only the rows that match a condition you write. Everything else is left out of the answer.

Keeping the rows that match

Put WHERE after the table's name, followed by a condition. Here, only products in the herbs category come back.

Example 1
SELECT name, category, priceFROM productsWHERE category = 'herbs';
name | category | price----------+----------+------- Rosemary | herbs | 6.00 Basil | herbs | 4.50 Mint | herbs | 4.00(3 rows)

Text values go in single quotes: 'herbs'. The = means "is equal to".

Comparing numbers

Numbers don't need quotes. You can compare them with < (less than), > (more than), <= and >=.

Example 2
SELECT name, priceFROM productsWHERE price < 8;
name | price-----------------+------- Rosemary | 6.00 Basil | 4.50 Clay Pot | 7.50 Mint | 4.00 Sunflower Seeds | 3.50(5 rows)

Only products cheaper than 8 come back. A product costing exactly 8 isn't included: for that you'd write price <= 8.

Not equal

<> means "is not equal to". PostgreSQL also accepts !=, which means the same.

Example 3
SELECT id, ordered, statusFROM ordersWHERE status <> 'delivered';
id | ordered | status----+------------+----------- 5 | 2025-02-20 | cancelled 8 | 2025-03-15 | shipped 9 | 2025-03-18 | shipped 10 | 2025-03-25 | pending 11 | 2025-03-27 | pending 12 | 2025-03-28 | pending(6 rows)

Comparing dates

Dates are written in quotes as year-month-day: '2025-03-01'. Comparing them works like comparing numbers, where later dates count as bigger.

Example 4
SELECT name, joinedFROM customersWHERE joined >= '2025-01-01';
name | joined------------+------------ Hugo Rossi | 2025-01-17 Isla Brown | 2025-02-08 Jon Berg | 2025-03-22(3 rows)

These are the customers who joined on or after the first of January, 2025.

Text must match exactly

Text comparisons care about capitals and spaces. 'Herbs' with a capital H matches nothing, because every category is stored in lowercase.

Example 5
SELECT nameFROM productsWHERE category = 'Herbs';
name------(0 rows)

An answer with no rows isn't an error. It means no row matched the condition.

Common mistakePutting text in double quotes. In SQL, double quotes are for names of columns and tables, so PostgreSQL looks for a column called Lisbon.
Example 6
SELECT name FROM customers WHERE city = "Lisbon";
ERROR: column "Lisbon" does not exist

Use single quotes for text: 'Lisbon'.

TipWrite WHERE on its own line. When a query gets longer, you can see straight away which rows it keeps.

Try it yourself

Run the code in the editor and answer 8 practice questions on filtering rows with where. It's free, you only need an account.

Practice this topic freeI have an account