Filtering & Sorting

Narrowing a query down to the rows you actually want with WHERE, and controlling the order results come back in with ORDER BY.

What is it?

SELECT * FROM orders gives you every single order — but most of the time you want something more specific: only orders from this month, only the five most expensive ones, only users whose name starts with "A." That's what filtering and sorting are for.

The WHERE clause narrows a query down to only the rows that match a condition you specify. The ORDER BY clause controls what order the matching rows come back in — ascending or descending, by any column you choose. They're often used together: filter down to the rows you care about, then sort them in a useful order.

Explain like I'm 10

WHERE is like sifting a basket of laundry down to just the socks. ORDER BY is then lining those socks up from smallest to biggest before you look at them. You can do either alone, or both together.

Examples

Filtering with WHERE

SELECT name, total_cents
FROM orders
WHERE total_cents > 5000;

Only returns orders whose total is more than 5000 cents ($50) — every other row is left out entirely.

Combining filtering, sorting, and limiting

SELECT name, total_cents
FROM orders
WHERE total_cents > 5000
ORDER BY total_cents DESC
LIMIT 3;

Finds orders over $50, sorts the matching ones from highest total to lowest (DESC means descending), then keeps only the top 3.

How it works

The database evaluates the WHERE condition against every row's actual values, keeping only the rows where it comes out true. Conditions can be combined with AND and OR for more complex logic. ORDER BY then takes the surviving rows and sorts them by one or more columns — ASC (ascending, the default) or DESC (descending). LIMIT can cap how many of the sorted results actually get returned.

Under the hood, without help from an index (a topic on its own), the database typically has to look at every row in the table to check the WHERE condition — how that lookup can be sped up is exactly what indexes are for.

Why does it exist?

Real applications almost never want "everything, in whatever order the database happens to store it in." Filtering and sorting exist so that the database itself can do the narrowing and ordering work, instead of your application code fetching every row and doing that filtering and sorting itself in memory — which would be both slower and far more code to write.

When to use it

Use WHERE any time you only want a subset of a table's rows. Use ORDER BY whenever the order results are presented in matters — recent-first activity feeds, cheapest-first product listings, alphabetical name lists.

When not to use it

If you genuinely need every row and the order truly doesn't matter (for instance, feeding all rows into a batch job that processes them in any order), skipping WHERE and ORDER BY avoids unnecessary work for the database.

Common mistakes

  • Forgetting that ORDER BY without DESC defaults to ascending order, which can look 'backwards' for things like dates when you want most-recent-first.

  • Filtering in application code after fetching all rows, instead of letting WHERE do it inside the database — much slower on large tables.

  • Assuming rows come back in a particular order automatically — without ORDER BY, the order isn't guaranteed at all.

Practice exercises

  1. Easy:

    Write a query that selects all products from a products table with a price less than 20.

  2. Medium:

    Write a query that returns the 5 most recently created rows from an orders table, assuming it has a created_at column.

  3. Hard:

    Write a query combining WHERE and ORDER BY to find the 3 cheapest in-stock products, assuming a products table with price and in_stock columns.

Interview questions

What does the WHERE clause do?

It filters which rows a query applies to, keeping only rows where the given condition evaluates to true.

What does ORDER BY control, and what's the default direction?

It controls the order results are returned in, by one or more columns; the default direction is ascending (ASC) unless DESC is specified.

If you don't use ORDER BY, is the order of returned rows guaranteed?

No — without an explicit ORDER BY, the database doesn't guarantee any particular row order.

What does `WHERE age = NULL` actually do, and why doesn't it match rows where age is NULL?

It matches nothing, ever. Comparing anything to NULL with = (or any comparison operator) doesn't evaluate to true or false — it evaluates to unknown, because NULL represents an unknown value and you can't know whether an unknown value equals anything. Finding NULL rows requires WHERE age IS NULL instead.

What are the three possible outcomes of evaluating a WHERE condition?

True, false, and unknown — unknown is typically produced whenever NULL is involved in a comparison. WHERE only keeps rows where the condition evaluates to true; both false and unknown rows are excluded from the results.

Given a table where some rows have age as NULL, what does `WHERE age > 20` return for those rows, and what about `WHERE NOT (age > 20)`?

Neither includes them. age > 20 evaluates to unknown for a NULL age, not false, and negating unknown with NOT still produces unknown, not true. Both queries silently exclude those rows rather than treating an unknown age as passing either check.

Why is `WHERE column <> 5` on a column containing some NULL values likely to exclude more rows than expected?

Rows where that column is NULL don't satisfy <> any more than they'd satisfy = — comparing NULL with anything yields unknown, not true — so NULL rows are silently dropped from both an equals-5 and a not-equals-5 result, even though intuitively a 'not equals' filter might seem like it should catch them.

What's a dangerous trap with `WHERE column NOT IN (list)` if that list can contain a NULL value?

If even one element of the IN list is NULL, NOT IN can end up matching zero rows at all for the entire query. NOT IN is effectively a chain of <> comparisons ANDed together, and comparing against NULL produces unknown for that one comparison, which poisons the whole chain to unknown — excluded — rather than true.

Does AND bind more tightly than OR in a WHERE clause, and why does that matter?

Yes. AND is evaluated before OR when there are no parentheses, so WHERE a = 1 OR b = 2 AND c = 3 is actually interpreted as WHERE a = 1 OR (b = 2 AND c = 3), not WHERE (a = 1 OR b = 2) AND c = 3. Relying on this precedence instead of writing explicit parentheses is an easy way to introduce a subtly wrong filter.

Is the range in `WHERE price BETWEEN 10 AND 20` inclusive or exclusive of 10 and 20?

Inclusive on both ends — BETWEEN is equivalent to price >= 10 AND price <= 20, so rows with price exactly 10 or exactly 20 are included.

What do `%` and `_` mean inside a LIKE pattern?

% matches any sequence of zero or more characters, and _ matches exactly one character, so LIKE 'A%' matches anything starting with A, while LIKE 'A_' matches exactly two characters starting with A.

Is LIKE guaranteed to be case-sensitive?

Not universally — it depends on the specific database and sometimes the column's collation. For example, PostgreSQL's LIKE is case-sensitive by default (with a separate case-insensitive ILIKE), while some other databases are case-insensitive by default for standard text, so this shouldn't be assumed to behave the same everywhere.

When ordering by two columns, like `ORDER BY last_name ASC, age DESC`, how does the second column affect the sort?

Rows are primarily sorted by last_name ascending. Only when two or more rows share the same last_name does age descending get used to break that tie — the second column never overrides the first, it only resolves ties within it.

Given the rows Kenji/34, Amara/29, and Amara/41, what order does `ORDER BY name ASC, age DESC` return them in?

Amara/41, then Amara/29, then Kenji/34. Rows are sorted by name alphabetically first (Amara before Kenji), and since both Amara rows tie on name, age DESC breaks the tie so the older Amara comes first.

Can each column listed in an ORDER BY clause have its own independent ASC/DESC direction?

Yes — direction is specified per column, so ORDER BY total_cents DESC, created_at ASC sorts primarily by highest total first, and only uses created_at (oldest first) to break ties among equal totals.

What does `LIMIT 5` do to a query's results?

It caps the number of rows returned to at most 5, discarding any additional matching (and, if present, sorted) rows beyond that count.

What does `OFFSET 10` do when combined with `LIMIT 5`?

It skips the first 10 matching, sorted rows, then returns up to the next 5 after that — the classic pattern for fetching a later page of results.

Why is it risky to use LIMIT/OFFSET for pagination without an ORDER BY clause?

Without ORDER BY, the database doesn't guarantee any particular row order between queries, so which rows land on 'page 1' versus 'page 2' isn't reliably consistent — the same row could appear on multiple pages, or never appear at all, especially if rows are also being inserted or deleted between requests.

What happens if OFFSET is larger than the total number of matching rows?

The query simply returns zero rows. It's not an error — it just means there's nothing left after skipping that many.

In terms of logical order of evaluation, does WHERE run before or after ORDER BY?

WHERE runs first, filtering down to the matching rows. Only those surviving rows get sorted by ORDER BY, and only after that does LIMIT, if present, truncate the final sorted list — filtering, then sorting, then capping.

Where do NULL values end up when sorting a column with ORDER BY — always first, or always last?

It's not universal — different databases default to different placement for NULLs (some put them first in ascending order, others last), so if the exact position matters, check the specific database's default or specify it explicitly, since many support NULLS FIRST or NULLS LAST.

Write a query that finds the 3 cheapest in-stock products from a products table with price and in_stock columns.

SELECT * FROM products WHERE in_stock = true ORDER BY price ASC LIMIT 3; — filters down to in-stock products, sorts the survivors from cheapest to most expensive, then keeps only the first three.

A query with `WHERE total_cents > 5000 ORDER BY total_cents DESC LIMIT 3` returns only 1 row. Is that a bug?

Not necessarily. LIMIT only caps the maximum rows returned. If WHERE matched only 1 row in total, that's all there is to sort and return, regardless of LIMIT 3 asking for up to three.

How would you get the rows ranked 2nd and 3rd by price, skipping the single cheapest, using LIMIT and OFFSET?

ORDER BY price ASC LIMIT 2 OFFSET 1 — sort cheapest first, skip the first row with OFFSET 1, then take the next two.

Why does filtering with WHERE inside the database scale better than fetching every row and filtering in application code?

The database can potentially use structures like indexes to jump straight to matching rows and never has to transfer non-matching rows over the network at all. Filtering in application code means every row, matching or not, has to be fetched, sent over the connection, and then discarded, which wastes both database and network work as the table grows.

Without any index, how does the database evaluate a condition like `WHERE total_cents > 5000`?

It generally performs a full table scan, checking the condition against every single row one by one, since there's no shortcut structure telling it which rows might qualify without looking. Indexes exist specifically to avoid this by letting the database narrow down candidate rows faster.

What's the difference between what WHERE excludes and what LIMIT excludes?

WHERE excludes rows based on whether they satisfy a condition — a row is kept or thrown out on its own merits. LIMIT excludes rows purely based on position in the already filtered and sorted result set, regardless of their content, purely to cap how many come back.

A developer expects `ORDER BY created_at DESC LIMIT 5 OFFSET 5` to always return a stable 'page 2' that never overlaps page 1, even as new orders keep being inserted. What can go wrong?

If new rows are inserted between the two requests, every existing row can shift down one position in the DESC ordering, which can cause the same row to reappear on both pages, or a row to be skipped entirely. LIMIT/OFFSET pagination is based on position, not a stable per-row marker, so it isn't safe against concurrent inserts or deletes affecting the exact ordering column being used.