SELECT and WHERE
A query describes the result; the database decides how
WHERE chooses rows, SELECT chooses columns, ORDER BY sorts. Without ORDER BY, row order is not guaranteed.
SQL is declarative. You describe the table you want back, and the database works out how to build it. Here, WHERE keeps only orders over two pounds, and SELECT keeps only two of the three columns. ORDER BY then sorts what is left, largest first. 4 rows come back. Notice what you did not say: which index to use, which order to read the table in. That is the optimiser's job, and lesson five shows how it does it.
SELECT and WHERE · Going deeper
The statement families: define, change, query, control
SQL statements fall into DDL (define), DML (change and query), DCL (permissions) and transaction control.
SQL statements fall into DDL (define), DML (change and query), DCL (permissions) and transaction control. DDL: data definition. CREATE a table, view, index or schema ALTER add a column, change a constraint DROP remove an object and its data TRUNCATE empty a table fast, without row-by-row logging DML: data manipulation. SELECT read rows (some books call this DQL) INSERT add rows UPDATE change rows that match a WHERE DELETE remove rows that match a WHERE MERGE insert or update in one statement (lesson 4) DCL: data control. GRANT SELECT ON orders TO analyst REVOKE take a permission back DENY SQL Server and Databricks: refuse even if granted elsewhere TCL: transaction control. BEGIN start a transaction COMMIT make its changes permanent ROLLBACK undo everything since BEGIN (lesson 5) An UPDATE or DELETE without a WHERE clause changes every row in the table. Run the WHERE as a SELECT first, and run the change inside a transaction you can roll back.
DDL: data definition
- CREATE a table, view, index or schema
- ALTER add a column, change a constraint
- DROP remove an object and its data
- TRUNCATE empty a table fast, without row-by-row logging
DML: data manipulation
- SELECT read rows (some books call this DQL)
- INSERT add rows
- UPDATE change rows that match a WHERE
- DELETE remove rows that match a WHERE
- MERGE insert or update in one statement (lesson 4)
DCL: data control
- GRANT SELECT ON orders TO analyst
- REVOKE take a permission back
- DENY SQL Server and Databricks: refuse even if granted elsewhere
TCL: transaction control
- BEGIN start a transaction
- COMMIT make its changes permanent
- ROLLBACK undo everything since BEGIN (lesson 5)
Joins
Four joins differ only in which unmatched rows they keep
INNER JOIN keeps matches; LEFT JOIN and RIGHT JOIN also keep one side’s unmatched rows; FULL JOIN keeps both.
A join pairs rows from two tables wherever the condition holds. Customers on one side, orders on the other, matched on customer id. Two rows have no partner: Dev, who never ordered, and order 106, a guest checkout with no customer. An inner join keeps only the 5 matched pairs. A left join also keeps every customer, so Dev appears with a NULL order. A right join keeps every order instead, so the guest order appears with a NULL name. A full join keeps both.
Joins · Going deeper · 1 of 2
Fan-out: a join repeats rows, and sums go wrong
A join repeats a row once per partner, so a total taken after the join can double count. Aggregate first, then join.
Cross, self, semi and anti joins · Going deeper · 2 of 2
Four more joins: every pairing, itself, has a match, has none
A semi join (EXISTS) keeps rows that have a match; an anti join (NOT EXISTS) keeps those with none; a cross join pairs everything.
Grouping
GROUP BY collapses rows into one row per group
GROUP BY makes one row per distinct value; aggregates such as COUNT, SUM and AVG summarise each group.
Questions like how much did each customer spend need grouping. The database gathers the orders that share a customer id: three for Ben, one each for Asha and Chen, and the guest order on its own. Each group becomes a single row, and the aggregates summarise it: a count, a sum, an average. 6 rows in, 4 rows out. Any column you select must be grouped or aggregated, because a group of three orders has no single order number.
Grouping · Going deeper · 1 of 2
Set operations: UNION, INTERSECT and EXCEPT
Set operations stack results with the same columns. UNION removes duplicates; UNION ALL keeps every row and is cheaper.
ROLLUP and CUBE · Going deeper · 2 of 2
One GROUP BY can return the subtotals and the grand total too
ROLLUP adds subtotals along a hierarchy, CUBE adds every combination, GROUPING SETS lists exactly the ones you want.
Logical query order
Clauses run in a different order from how they are written
FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY: WHERE filters rows before grouping, HAVING filters groups after.
A query is written starting with SELECT, but it is evaluated starting with FROM. Follow the lines. The tables come first, then the join, then WHERE filters rows, then rows are grouped, then HAVING filters the groups, and only then is SELECT computed. That is why WHERE cannot use a column alias or an aggregate: neither exists yet. And it is why ORDER BY can, because it runs last.
NULL
A comparison with an unknown is itself unknown
SQL uses three-valued logic: a comparison with NULL is unknown, and WHERE keeps only rows that are true.
NULL is where most SQL surprises come from. It means unknown, so asking whether it equals anything, even another NULL, gives unknown. Only IS NULL gives a straight answer, and COALESCE swaps in a default. So when you ask for customers not in Leeds, Chen disappears: her city might be Leeds, and WHERE only keeps rows it is sure about. Counting behaves the same way. COUNT star counts 4 rows. COUNT city counts 3 known cities. COUNT DISTINCT city counts 2.
Lesson complete
You finished “SQL basics”
Nicely done.