SQL Fundamentals

The building blocks of every query: SELECT, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT, the order they run in, and how NULL changes each one.

Overview

Almost every SQL interview question is built from six clauses: SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY, plus LIMIT. You write them in one order, but the database runs them in another. Most wrong answers come from forgetting that order, or from forgetting what NULL does.

This page uses one tiny table, so every query below shows its exact result. All the SQL runs on PostgreSQL.

The sample table

Each row is an employee. Two values are missing on purpose: Eli has no salary and Fay has no department. Missing values are stored as NULL, which means 'unknown'.

1 — Ana — Eng — 150000 — NULL
2 — Ben — Eng — 120000 — 1
3 — Cai — Eng — 120000 — 1
4 — Dee — Sales — 90000 — 1
5 — Eli — Sales — NULL — 4
6 — Fay — NULL — 70000 — 1

The order a query runs in

The database first finds the rows (FROM), then drops the rows it does not want (WHERE), then forms groups (GROUP BY) and drops groups (HAVING). Only then does it compute the output columns (SELECT). Sorting and LIMIT come last. The PostgreSQL manual lists the same steps for SELECT.

This order explains the rules that trip people up.

WHERE — before grouping — No — No
GROUP BY — forms groups — Yes, in PostgreSQL — No
HAVING — after grouping — No — Yes
ORDER BY — after SELECT — Yes — Yes

So SELECT salary * 12 AS yearly FROM employees WHERE yearly > 100000 fails with 'column "yearly" does not exist', because WHERE runs before the alias is made. Repeat the expression in WHERE, or wrap the query in a subquery. ORDER BY yearly works, because sorting runs after SELECT.

WHERE filters rows, HAVING filters groups

WHERE looks at one row at a time. HAVING looks at one group at a time, so it can test an aggregate such as COUNT(*). An aggregate function (COUNT, SUM, AVG, MIN, MAX) turns many rows into one value per group.

Eng — 3 — 130000

Follow the rows through each step. The diagram shows why Sales disappears even though it has two employees.

Put a condition in WHERE whenever it is about single rows. It removes rows before the grouping work starts. Use HAVING only for conditions on the group, such as a count or an average.

Every column in the SELECT list must be grouped or inside an aggregate. SELECT dept, name FROM employees GROUP BY dept fails, because a group has many names and the database cannot pick one. PostgreSQL makes one exception: if you group by a table's primary key, you may select that table's other columns.

NULL: the unknown value

NULL is not zero and not an empty string. It means the value is unknown, so most comparisons with it are also unknown. WHERE keeps a row only when its condition is true; unknown counts as not true.

NULL = NULL — NULL — two unknowns are not known to be equal
NULL IS NULL — true — the only reliable test for NULL
5 + NULL — NULL — arithmetic with unknown is unknown
'Eng' <> NULL — NULL — so the row is filtered out
NULL IS DISTINCT FROM 'Eng' — true — treats NULL as a normal value
FALSE AND NULL — false — false no matter what the unknown is
TRUE OR NULL — true — true no matter what the unknown is

This is the classic trap. Asked for everyone outside Eng, most people write dept <> 'Eng', and Fay goes missing.

The aggregate functions skip NULL inputs. COUNT(*) counts rows, while COUNT(salary) counts only rows where salary is not NULL. AVG(salary) divides by 5 here, not 6.

6 — 5 — 2 — 550000 — 110000

One more surprise: over zero rows, SUM returns NULL, not 0. Only COUNT returns 0. Wrap it in COALESCE, which returns its first argument that is not NULL.

NULL — 0 — 0

Sorting and paging

ORDER BY sorts ascending unless you write DESC. In PostgreSQL, NULL sorts as if it were larger than every value: last in ascending order and first in descending order. So a 'top 3 salaries' query can return a person with no salary at the top.

Eli — NULL
Ana — 150000
Ben — 120000

Add NULLS LAST, and add a unique column (id) so ties always come out in the same order.

Ana — 150000
Ben — 120000
Cai — 120000

Without ORDER BY, LIMIT returns whichever rows the plan happens to produce first, and that can change between runs. For pages, there are two common ways.

OFFSET — ORDER BY id LIMIT 20 OFFSET 40 — the server still computes the 40 skipped rows, so deep pages get slower
Keyset — WHERE id > 40 ORDER BY id LIMIT 20 — an index on id jumps straight to the start of the page

Keyset paging remembers the last key the client saw and asks for rows after it.

3 — Cai
4 — Dee

What you will be able to do

Say the order the clauses run in, and use it to explain why a query fails.
Choose between WHERE and HAVING for any filter.
Predict what NULL does to comparisons, counts, sums and sorting.
Write top-N and paging queries that return the same rows every time.

When to use it

Reading chosen columns, or values computed from them, out of a table.
Keeping only the rows that match a condition (WHERE).
Summarizing rows per category with COUNT, SUM or AVG (GROUP BY).
Keeping only the groups that pass a test on an aggregate (HAVING).
Sorting results and returning one page at a time (ORDER BY, LIMIT).
Removing duplicate rows from a result (DISTINCT).

Interview questions

Explain the logical order of a SELECT and why it matters.
What is the difference between WHERE and HAVING?
How do you handle NULL values in SQL?

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio