Filtering with WHERE

Tables can hold millions of rows, and you rarely want all of them. The WHERE clause keeps only the rows that match a condition. It's the main tool for finding data — and, later, for deciding which rows an UPDATE or DELETE affects.

A basic filter

SELECT name, price
FROM products
WHERE category = 'Home';

The condition is checked for each row; rows where it's true are kept. Text values go in single quotes; numbers don't.

Comparison operators

<> is the standard "not equal"; != is supported almost everywhere too.

SELECT name, stock
FROM products
WHERE stock <= 12;

Comparing text

SELECT name, country
FROM customers
WHERE country <> 'Portugal';

Text comparisons compare characters in order, so < and > work alphabetically. Whether they are case-sensitive depends on the database and its collation: SQLite and PostgreSQL compare case-sensitively by default ('lisbon' doesn't equal 'Lisbon'), while MySQL and SQL Server usually don't.

SELECT name FROM customers WHERE city = 'lisbon';

No rows: the stored value is 'Lisbon'. Lesson 14 shows how to compare case-insensitively.

Comparing dates

SELECT id, order_date, status
FROM orders
WHERE order_date >= '2026-02-01';

Dates written in ISO format (YYYY-MM-DD) compare correctly in every database. In SQLite, dates are stored as text, and the ISO format makes alphabetical order the same as date order.

Filtering on calculated values

SELECT name, price, stock, ROUND(price * stock, 2) AS stock_value
FROM products
WHERE price * stock > 2000;

You can filter on any expression. (ROUND(…, 2) tidies the result to two decimal places — lesson 15 explains why it's needed.) Note that you generally can't use the alias stock_value in WHERE: the database evaluates WHERE before it builds the select list. (SQLite and MySQL allow it as an extension, but standard SQL doesn't.)

WHERE in other statements

UPDATE products SET price = 22.50 WHERE id = 1;
DELETE FROM orders WHERE status = 'cancelled';

The same conditions decide which rows are changed or deleted. Forgetting the WHERE on an UPDATE or DELETE changes every row — one of the classic SQL accidents (lesson 36).

Try it yourself

Find all employees earning more than 70,000.