SELECT, WHERE and logical order

The clauses

SELECT   customer_id, order_total
FROM     orders
WHERE    order_date >= '2024-01-01'
ORDER BY order_total DESC
LIMIT    10;

LIMIT is PostgreSQL/MySQL/SQLite. SQL Server uses TOP 10 after SELECT, or OFFSET ... FETCH. Oracle uses FETCH FIRST 10 ROWS ONLY. Interviewers often ask which dialect you know — say so explicitly.

Logical processing order

You write a query in one order and the database evaluates it in another:

This single list answers a lot of interview questions. SELECT runs fifth, which is why an alias defined there is not visible to WHERE:

-- fails
SELECT price * qty AS revenue FROM sales WHERE revenue > 100;

Repeat the expression in WHERE, or wrap the query in a subquery or CTE. ORDER BY runs after SELECT, so aliases do work there.

NULL is not a value

NULL means unknown, so comparisons return unknown rather than true or false. WHERE col = NULL matches nothing — use IS NULL / IS NOT NULL.

The subtle one: NOT IN with a NULL in the list returns no rows at all, because the comparison can never be proven true. NOT EXISTS does not have this problem, which is why it is the safer default.

COALESCE(col, 0) substitutes a fallback. Aggregates ignore NULLs — AVG divides by the count of non-null values, not the row count, which is a common source of surprise.

Filtering specifics

IN (…) for a value list, BETWEEN a AND b inclusive on both ends, LIKE 'abc%' for prefix matching (a leading % usually prevents index use).

For dates, prefer a half-open range — >= '2024-01-01' AND < '2024-02-01' — over BETWEEN, which includes the endpoint and will silently drop or double-count timestamps at midnight.