Databases

Filtering Rows (WHERE)

O(n) time — the database checks every row once. This is much faster with an index (see the Indexes lesson). O(1) extra space if you filter in place.

The idea, in plain English

A database table is like a spreadsheet. Each row is one record. Each column is one field. WHERE works like a filter button. It hides every row that does not match your condition. It shows you only the rows that match.

How it works

1Look at each row, one at a time.
2Check the row against your condition, such as age > 30. Keep the row if it passes. Skip it if it does not.
3Collect only the rows that passed. This is your filtered result set.

When you'd use it

Use WHERE any time you want part of your data, not all of it. For example: 'find all users over 30' or 'find orders over $100'. It is the most-used part of SQL.

Common beginner mistakes

WHERE compares one row at a time. It cannot see a total computed across many rows. Use HAVING for that, after GROUP BY.
Do not assume = ignores upper and lower case in text. 'Ana' and 'ana' often do not match.

Try it — edit and run

Click the code to edit · press ⌘/Ctrl+↵ to run

Expected output — hit Run to try it

All users:
1 Ana 28 Lahore
2 Bilal 35 Karachi
3 Cara 41 Lahore
4 Dan 22 Multan
SQL: SELECT * FROM users WHERE age > 30
Matching rows:
2 Bilal 35 Karachi
3 Cara 41 Lahore

Not sure this is the right topic? See the learning paths → or where this leads →