Filtering & Sorting: WHERE, ORDER BY, LIMIT

Returning every row was fine when the table had five of them. Real tables don't. You almost never want all the users - you want the ones in London, or over 40, or the single most recently created account. This phase is where SQL gets genuinely useful: you describe the rows you want, and the database finds them for you.

We'll keep working with the same users table from Phase 1:

 id │ name           │ email                 │ city        │ age │ created_at
────┼────────────────┼───────────────────────┼─────────────┼─────┼────────────
  1 │ Ada Lovelace   │ [email protected]       │ London      │  36 │ 2026-01-04
  2 │ Grace Hopper   │ [email protected]     │ New York    │  41 │ 2026-01-09
  3 │ Alan Turing    │ [email protected]      │ London      │  29 │ 2026-02-15
  4 │ Katherine J.   │ [email protected]       │ Hampton     │  52 │ 2026-03-01
  5 │ Linus T.       │ [email protected]     │ Portland    │  33 │ 2026-03-22

WHERE - keep only the rows that match

What it actually is. WHERE is a filter - the difference between "all users" and "users I care about." You give it a condition, a true-or-false test, and the database checks every row against it, keeping only the rows where the test comes out true.

A real example.

SELECT name, city
FROM users
WHERE city = 'London';

 name           │ city
────────────────┼────────
 Ada Lovelace   │ London
 Alan Turing    │ London

What just happened: The database tested each row's city against 'London'. Rows 1 and 3 passed, the rest failed, so they're not in the result. Note the single quotes around 'London' - text values go in single quotes in SQL, numbers don't: you'd write WHERE age = 36, no quotes.

📝 Terminology. A condition (or predicate) is the true/false test in a WHERE. city = 'London' is true for some rows, false for others; WHERE keeps the true ones.

Comparison operators: =, >, <, and friends

The most common tests compare a column to a value:

SELECT name, age
FROM users
WHERE age > 40;

 name           │ age
────────────────┼─────
 Grace Hopper   │  41
 Katherine J.   │  52

What just happened: The test age > 40 was true only for Grace (41) and Katherine (52), so those are the two rows you got. Everyone 40 or younger was filtered out.

Try a filter yourself on the built-in authors table:

SELECT name, country
FROM authors
WHERE country = 'USA';

What just happened: The database tested each row's country against 'USA' and kept only the matches - Grace Hopper and Dennis Ritchie. Note the single quotes: text values go in 'single quotes'.

LIKE - match part of a text value

What it actually is. LIKE is for "contains" or "starts with" style matching on text, using % as a wildcard meaning "any run of characters (including none)."

A real example.

SELECT name, email
FROM users
WHERE email LIKE '%@example.com';

 name           │ email
────────────────┼───────────────────────
 Ada Lovelace   │ [email protected]
 Grace Hopper   │ [email protected]
 Alan Turing    │ [email protected]
 Katherine J.   │ [email protected]
 Linus T.       │ [email protected]

What just happened: %@example.com means "anything, followed by @example.com." Every address ends that way, so every row matched. 'A%' means "starts with capital A"; '%lan%' means "contains lan anywhere." The % is the workhorse here.

⚠️ Gotcha. LIKE matching is often case-sensitive - but whether it is depends on your database and its settings. In some setups 'a%' won't match Ada. If a LIKE returns fewer rows than you expect, case is the first thing to check.

IN - match any value from a list

What it actually is. IN is a tidy shorthand for "equals any of these." Instead of stringing together city = 'London' OR city = 'Portland', you write the list once.

A real example.

SELECT name, city
FROM users
WHERE city IN ('London', 'Portland');

 name           │ city
────────────────┼──────────
 Ada Lovelace   │ London
 Alan Turing    │ London
 Linus T.       │ Portland

What just happened: IN ('London', 'Portland') matched any row whose city is either of those two - the same result as city = 'London' OR city = 'Portland', just shorter and easier to read as the list grows.

Combining conditions with AND / OR

What it actually is. AND means "both must be true"; OR means "at least one must be true." You chain conditions together to describe more specific rows.

A real example.

SELECT name, city, age
FROM users
WHERE city = 'London' AND age < 35;

 name           │ city    │ age
────────────────┼─────────┼─────
 Alan Turing    │ London  │  29

What just happened: A row had to pass both tests - in London and under 35. Ada is in London but she's 36, so she failed the second test. Only Alan satisfied both.

⚠️ Gotcha. When you mix AND and OR in one WHERE, AND binds tighter than OR - so A OR B AND C reads as A OR (B AND C), often not what you meant. When in doubt, add parentheses: (A OR B) AND C. They cost nothing and remove all ambiguity.

The NULL trap - use IS NULL, never = NULL

This one confuses everybody the first time, so let's name it clearly.

📝 Terminology. NULL is SQL's way of saying "no value here - unknown / not set." It is not zero, and not an empty string. It's the absence of a value.

Here's the part that trips people: in SQL, NULL is not equal to anything - not even to another NULL. The reasoning: NULL means "unknown," and "is one unknown thing equal to another unknown thing?" can't truthfully be answered yes. So any comparison with NULL using = comes out "unknown," which WHERE treats as not-a-match.

That means this does not work the way it looks:

-- WRONG: this returns no rows, even if some emails are missing
SELECT name
FROM users
WHERE email = NULL;

 name
──────
(0 rows)

What just happened: email = NULL is never true (it's "unknown" for every row), so WHERE kept nothing - zero rows even if missing emails exist. No error, just silently empty, which is exactly why this bites people.

To actually test for missing values, SQL gives you IS NULL (and IS NOT NULL):

-- RIGHT: this finds rows where email has no value
SELECT name
FROM users
WHERE email IS NULL;

What just happened: IS NULL is the proper test for "this value is absent." Use IS NULL to find missing values and IS NOT NULL to find present ones. (Our sample users all have emails, so this returns no rows here - but on a table with gaps, this is how you find them.)

💡 Key point. Never compare to NULL with =, <>, >, etc. - those always come out "unknown." Use IS NULL / IS NOT NULL. The day a query mysteriously returns nothing, ask yourself: "am I accidentally comparing against NULL?"

ORDER BY - put the rows in order

What it actually is. Without ORDER BY, the database is free to hand back matching rows in any order it finds convenient - you can't rely on it. ORDER BY lets you say "sort the result by this column."

A real example.

SELECT name, age
FROM users
ORDER BY age DESC;

 name           │ age
────────────────┼─────
 Katherine J.   │  52
 Grace Hopper   │  41
 Ada Lovelace   │  36
 Linus T.       │  33
 Alan Turing    │  29

What just happened: ORDER BY age DESC sorted by age, highest first. DESC means descending (big → small); ASC means ascending (small → big) and is the default if you write neither - so ORDER BY age alone would put Alan (29) at the top.

⚠️ Gotcha. If you want a dependable order, say so with ORDER BY. Rows coming back "in order" without it is luck, not a guarantee, and that luck can change when the data or the database does.

LIMIT - take only the first few rows

What it actually is. LIMIT caps how many rows come back. Paired with ORDER BY, it answers "the top N" questions - the newest order, the five highest scores, the oldest account.

A real example.

SELECT name, created_at
FROM users
ORDER BY created_at DESC
LIMIT 3;

 name           │ created_at
────────────────┼────────────
 Linus T.       │ 2026-03-22
 Katherine J.   │ 2026-03-01
 Alan Turing    │ 2026-02-15

What just happened: You sorted by created_at newest-first, then LIMIT 3 kept only the first three - the three most recently created users. Without the ORDER BY, "the first 3" would be meaningless; LIMIT takes the first rows of whatever order you've established, so it almost always travels with ORDER BY.

📝 Terminology note. LIMIT is what PostgreSQL, MySQL, and SQLite use. SQL Server uses TOP (SELECT TOP 3 ...); Oracle has its own syntax (FETCH FIRST 3 ROWS ONLY). Same idea everywhere, the keyword differs - we'll use LIMIT throughout.

The order of the clauses

These pieces always go in the same order. The database expects this sequence, and writing them out of order is a syntax error:

Not every query needs every clause, but when they appear together, this is the order: pick the table, filter to the rows you want, sort them, then take the top few.

Recap

1. WHERE keeps only rows where a condition is true. Text values go in 'single quotes'; numbers don't.
2. Compare with =, <>, >, <, >=, <=; match text patterns with LIKE and %; match a list with IN; combine with AND / OR (parenthesize when you mix them).
3. NULL is not equal to anything - use IS NULL / IS NOT NULL, never = NULL.
4. ORDER BY sorts the result (ASC default, DESC for reverse); without it, order isn't guaranteed.
5. LIMIT n takes the first n rows - pair it with ORDER BY to get a meaningful "top N."
6. The clauses go in a fixed order: SELECT → FROM → WHERE → ORDER BY → LIMIT.

You can now read exactly the data you want. Next comes the other half of SQL - changing it - where the stakes go up and one missing word can rewrite an entire table. We'll make sure it never catches you.

← Phase 1: Asking for Data · Guide overview · Phase 3: Changing Data →

Try it yourself

This runs real SQLite in your browser against a tiny authors table - edit and run it:

SELECT name, country FROM authors WHERE country = 'UK' ORDER BY name;