SELECT, WHERE and the query lifecycle
Read a query the way the engine reads it, and filter rows without guessing.
- Write a SELECT with column projections, aliases and computed expressions
- Filter rows with WHERE using comparison operators, IN, BETWEEN, LIKE and NULL checks
- Order and limit results with ORDER BY and LIMIT/OFFSET
- Explain the logical order in which a SQL query is evaluated
A product manager asks you for "top 10 customers by revenue in the last quarter who have not returned an order". That is one SELECT with a join, a GROUP BY, a HAVING, an ORDER BY and a LIMIT. Every clause has a fixed position in the query and a fixed position in the engine's evaluation order. Learn that order once, and multi-clause questions stop being intimidating.
A SELECT statement answers: which rows, which columns, in what order, how many.
The logical evaluation order (not the written order):
- FROM / JOIN — identify the source rows.
- WHERE — filter rows (before grouping).
- GROUP BY — collapse rows into groups.
- HAVING — filter groups (after grouping).
- SELECT — choose columns and compute expressions.
- DISTINCT — remove duplicate rows.
- ORDER BY — sort the result.
- LIMIT / OFFSET — cut the result.
This is why you cannot use a column alias inside WHERE (the alias is only resolved at step 5) and why WHERE filters rows while HAVING filters groups.
NULL is three-valued logic. NULL means "unknown", not zero or empty string. Comparisons with NULL return NULL (treated as false), so WHERE col = NULL never matches. Use IS NULL / IS NOT NULL instead. Similarly, aggregates ignore NULLs: COUNT(col) skips NULLs, COUNT(*) counts rows.
| Operator | Works with | Example |
|---|---|---|
| =, <>, <, >, <=, >= | Numbers, strings, dates | age >= 18 |
| IN (...) | Lists | status IN ('active', 'pending') |
| BETWEEN a AND b | Inclusive ranges | salary BETWEEN 30000 AND 50000 |
| LIKE pattern | Strings; % any run, _ one char | name LIKE 'A%' |
| IS NULL / IS NOT NULL | Any column | email IS NOT NULL |
`WHERE salary = NULL` returns zero rows even when NULLs exist, because NULL = NULL is not true. Always write `IS NULL`.
Explicit columns make queries readable, use indexes, and stop schema changes upstream from breaking your code. In interviews, always name the columns.
- 1
Identify source and filters
Source: orders. Filters: status = paid, amount > 1000, created_at in 2025.
WHERE status = 'paid' AND amount > 1000 AND created_at >= '2025-01-01' AND created_at < '2026-01-01' - 2
Project only the needed columns
Select id and amount. Add a computed column for amount with GST if wanted.
SELECT id, amount - 3
Order and limit
Newest first: created_at DESC. Top 10: LIMIT 10.
ORDER BY created_at DESC LIMIT 10
Solution
SELECT id, amount FROM orders WHERE status = 'paid' AND amount > 1000 AND created_at >= '2025-01-01' AND created_at < '2026-01-01' ORDER BY created_at DESC LIMIT 10;
1. List names of employees in department 3 earning more than 50000. 2. Find products whose name starts with "Pro" and price is between 100 and 500. 3. List all users with a NULL phone number, ordered alphabetically by name.
Hint: 1: SELECT name FROM employees WHERE department_id = 3 AND salary > 50000. 2: WHERE name LIKE 'Pro%' AND price BETWEEN 100 AND 500. 3: WHERE phone IS NULL ORDER BY name.
Using = with NULL
NULL = NULL is not true. Use IS NULL / IS NOT NULL.
Putting aggregates in WHERE
WHERE filters rows before grouping. Aggregate conditions belong in HAVING.
Forgetting that LIKE is case-sensitive in some engines
PostgreSQL LIKE is case-sensitive; use ILIKE or LOWER() for case-insensitive matching. MySQL default collation is usually case-insensitive.
Why does `SELECT * FROM t WHERE col <> NULL` return no rows even when col has NULLs?
Any comparison with NULL evaluates to UNKNOWN, not true or false. WHERE only keeps rows where the condition is TRUE, so UNKNOWN rows are dropped. Use IS NOT NULL instead.
Source: Standard interview warmup
- Logical order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT.
- NULL is unknown; use IS NULL, never = NULL.
- SELECT explicit columns; never SELECT * in production code.
- WHERE filters rows; HAVING filters groups.
