Asking a database questions with SQL
SQL is the language you use to ask a database for data. You describe what you want, and the database works out how to fetch it. In this lesson you build queries with clicks and watch each step happen to real rows.
SQL is declarative: you describe the result set, and the planner decides how to compute it. This lesson covers filtering and sorting, join semantics, aggregation and window functions, the logical order of evaluation, three-valued logic and injection.
The shop data used in every unit
NULL means "no value here". Eli did not give a city, one order was placed by a guest without an account, and some orders have no rating yet. Gus and Hana have not ordered anything.
NULL marks a missing or unknown value: customers.city for Eli, orders.customer_id for a guest checkout, and unrated orders. Gus and Hana have no orders, and no order references the desk lamp.
SELECT, WHERE, ORDER BY, LIMIT
Build a query by clicking. Pick a table, choose which columns to show, add a filter, a sort order and a row limit. Then press Run and watch what happens to each row.
Compose a single-table query. Dashed options are advanced cases: NULL comparisons, ranges and OFFSET. Watch for the notes on NULL ordering and on ties at the LIMIT boundary.
SELECT these columns FROM this table, but only rows WHERE this is true, sorted by ORDER BY, and give me at most LIMIT rows." Text values go in single quotes, like 'Berlin'. Numbers do not.
- Without
ORDER BY, row order is undefined. It can change between runs, versions and plans. - NULL placement differs: PostgreSQL and Oracle sort NULLs last in ASC, MySQL and SQLite sort them first. Say
NULLS FIRSTorNULLS LASTwhere supported. - Pagination with
LIMIT … OFFSETneeds a unique sort key, or rows can repeat or vanish between pages. For deep pages, prefer keyset pagination:WHERE (price, id) > (?, ?). SELECT *is fine for exploring, but name columns in application code so schema changes do not break it.
JOINs: combining tables
Orders only store a customer number. To show the customer's name next to each order, you join the two tables: every order is matched to the customer whose id equals its customer_id. Pick a join type and follow the lines.
The join predicate pairs rows. Outer joins then add back unmatched rows from one or both sides, padded with NULLs. Try all four types, then see what a filter on the right table does to a LEFT JOIN.
INNER JOIN when you only want rows that have a partner, such as "orders with their customer". Use LEFT JOIN when every row on the left must appear even without a partner, such as "all customers, and their orders if they have any". Missing values come back as NULL.
RIGHT JOINis a LEFT JOIN with the tables swapped. Most teams write only LEFT JOINs to keep queries readable.- Conditions about the optional side belong in
ON. Conditions inWHERErun after the join and reject the NULL-padded rows. - To find rows with no partner (an anti-join), use
LEFT JOIN … WHERE o.id IS NULLorNOT EXISTS. - A 1:N join repeats the left row for each match. Aggregating after it (for example
SUMof a customer column) can double count.
GROUP BY and aggregates
GROUP BY sorts rows into buckets that share a value, then turns each bucket into one summary row. COUNT counts, SUM adds up and AVG takes the average. WHERE filters rows before the buckets. HAVING filters whole buckets after.
Aggregation collapses each group to one row. Compare COUNT(*) with COUNT(rating) and see that AVG skips NULLs and that NULL keys form a single group. Then compare GROUP BY with window functions, which keep every row.
COUNT. A bucket of three orders has three amounts, so the database needs to know whether you want their sum, their average or something else.
GROUP BY versus window functions
A window function computes over a set of related rows, like a group, but does not collapse them. Each row keeps its identity and gets the extra value next to it.
How a query really runs
You write SELECT first, but the database does not start there. It first finds the table, then filters, then groups, and only then picks the columns. Step through the query below and watch the numbers next to each line.
The logical processing order defines what each clause can see. The physical plan may push filters down, reorder joins or use an index, but it must produce the same result as this order.
Why does this fail?
NULL and three-valued logic
In SQL a condition can be TRUE, FALSE or NULL (unknown). WHERE keeps a row only when the condition is TRUE. Predict each result.
SQL injection and parameterized queries
A search box sends a name to the server, which builds a query with it. Try the presets, and switch between gluing strings together and passing the value as a parameter.