SELECT and WHERE

A query describes the result; the database decides how

SELECT order_id, totalFROM ordersWHERE total > 2ORDER BY total DESC;ordersorder_idcustomer_idtotal10121.2010216.5010323.0010438.7510521.20106NULL4.00WHERE drops rowswith total ≤ 2 (struck out)SELECT dropsthe customer_id column (dashed)resultorder_idtotal1048.751026.501064.001033.00ORDER BY sortswhat is leftA query describes the result you want; the database decides how to get it.WHERE chooses rows, SELECT chooses columns, ORDER BY sorts. 4 of 6 orders pass the filter. A result is itself a table, so it can be filtered,joined or saved as a view in turn. Without ORDER BY a database may return rows in any order, and the order can change between runs.

WHERE chooses rows, SELECT chooses columns, ORDER BY sorts. Without ORDER BY, row order is not guaranteed.

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.

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.

Joins

Four joins differ only in which unmatched rows they keep

customersidname1Asha2Ben3Chen4Devordersordercustomer_id10121021103210431052106NULLall four: ON customers.id = orders.customer_idINNER JOIN5 rows: matches onlynameorderBen101Asha102Ben103Chen104Ben105LEFT JOIN6 rows: + DevnameorderBen101Asha102Ben103Chen104Ben105DevNULLRIGHT JOIN6 rows: + order 106nameorderBen101Asha102Ben103Chen104Ben105NULL106FULL JOIN7 rows: + bothnameorderBen101Asha102Ben103Chen104Ben105DevNULLNULL106A join pairs rows that match; an outer join also keeps the rows that found no partner, padded with NULL.Dev has no orders and order 106 was a guest checkout. INNER drops both; LEFT keeps Dev; RIGHT keeps the guest order; FULL keeps everyone.

INNER JOIN keeps matches; LEFT JOIN and RIGHT JOIN also keep one side’s unmatched rows; FULL JOIN keeps both.

Joins · Going deeper · 1 of 2

Fan-out: a join repeats rows, and sums go wrong

customersnamecreditBen50.00×orders (Ben’s)ordercustomer_id101210321052customers JOIN ordersnamecreditorderBen50.00101Ben50.00103Ben50.00105SUM(credit) = 150.00Ben’s credit is 50.00Other joins worth knowingCROSS JOIN every row with every row: 4 customers × 6 orders = 24 rowsSELF JOIN a table joined to itself: employees to their managersA join repeats each row once for every partner it finds, so totals taken after a join can count the same value many times.This fan-out is the most common wrong answer in reporting. Aggregate each table to the grain you need first, then join; or join on a keythat is unique on one side. A row count that grows after a join is the warning sign.

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

CROSS JOIN: every row with every rowSELECT size, colourFROM sizes CROSS JOIN colours;3 sizes × 2 colours = 6 rows: S red, S blue, M red, M blue, L red, L blueSelf join: a table joined to itselfSELECT c.name, r.name AS referred_byFROM customers cLEFT JOIN customers r ON r.id = c.referred_by;Asha: NULL · Ben: Asha · Chen: Asha · Dev: BenSemi join: rows that have a match, each onceSELECT name FROM customers cWHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Asha, Ben, Chen. Ben appears once, though he has 3 ordersAnti join: rows that have no matchSELECT name FROM customers cWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Dev. In the plan: Hash Right Anti Join✗WHERE id NOT IN (SELECT customer_id FROM orders) → 0 rows,because order 106 has a NULL customerA semi or anti join filters one table by another without repeating its rows or adding columns.Standard SQL writes them as EXISTS and NOT EXISTS; Spark SQL and Databricks also accept LEFT SEMI JOIN and LEFT ANTI JOIN,and every planner shows them as Semi Join or Anti Join. A cross join multiplies: 1,000 × 1,000 rows is 1,000,000, and a joinwhose ON condition is missing or always true is a cross join by accident. USING (customer_id) is short for ON a.customer_id= b.customer_id; avoid NATURAL JOIN.

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

SELECT customer_id, COUNT(*), SUM(total), AVG(total) FROM orders GROUP BY customer_id;ordercustomer_idtotal10216.5010121.2010323.0010521.2010438.75106NULL4.00customer_idCOUNT(*)SUM(total)AVG(total)116.506.50235.401.80318.758.75NULL14.004.00the NULL guest orderforms a group of its ownGROUP BY collapses the rows that share a value into one row, and aggregates summarise each group.6 orders become 4 rows. Every column in SELECT must either be grouped or inside an aggregate: COUNT, SUM, AVG, MIN, MAX.Asking for order_id here is an error, because a group of three orders has no single order_id. Aggregates skip NULL inputs, except COUNT(*),which counts rows. All the NULL keys fall into one group, even though NULL is never equal to NULL anywhere else.

GROUP BY makes one row per distinct value; aggregates such as COUNT, SUM and AVG summarise each group.

Grouping · Going deeper · 1 of 2

Set operations: UNION, INTERSECT and EXCEPT

customers.citycityPuneLeedsLeedssuppliers.citycityLeedsPerthUNIONboth, duplicates removedcityPuneLeedsPerthUNION ALLboth, every row keptcityPuneLeedsLeedsLeedsPerthINTERSECTin bothcityLeedsEXCEPTin the first onlycityPuneSet operations stack results that have the same columns: union, intersection and difference.A join puts tables side by side; a set operation puts them one above the other. UNION removes duplicates, which costs a sort, so use UNION ALLwhen the inputs cannot overlap or duplicates are wanted. EXCEPT is called MINUS in Oracle and Snowflake accepts both.

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

salesregionproductamountNorthPen120NorthInk290SouthPen200SouthInk180SouthPen905 rows, 880 in allROLLUP (region, product)regionproductsumNorthInk290NorthPen120NorthNULL410SouthInk180SouthPen290SouthNULL470NULLNULL8804 groups + a subtotal per region+ the grand total = 7 rowsCUBE (region, product)the same 7 rows, plus asubtotal per product:regionproductsumNULLInk470NULLPen4109 rows: every combinationGROUPING(region) = 1marks a NULL that means “allregions”, not a missing regionGROUPING SETS ( (region), (product), () )regionproductsumNorthNULL410SouthNULL470NULLInk470NULLPen410NULLNULL880only the totals you list: 5 rowsROLLUP adds subtotals along a hierarchy, CUBE every combination, GROUPING SETS only those you list.One pass over the table replaces several GROUP BY queries glued together with UNION ALL. ROLLUP (year, month, day) is theusual report; n columns give n + 1 levels, while CUBE gives 2ⁿ. The subtotal rows carry NULL in the rolled-up column; useGROUPING() to label them. To turn rows into columns, put the condition inside the aggregate: SUM(CASE WHEN product = 'Pen'THEN amount END) AS pen. Results from PostgreSQL 16.

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

As writtenAs evaluatedSELECTFROMJOINWHEREGROUP BYHAVINGORDER BYLIMITFROM1 pick the tablesJOIN2 pair their rowsWHERE3 filter rowsGROUP BY4 form groupsHAVING5 filter groupsSELECT6 compute columns, aliasesORDER BY7 sortLIMIT8 cut to n rowsWHERE runs early:no aliases andno aggregates yetORDER BY runs late:aliases workSQL is evaluated in a different order from how it is written, and that order explains most of its rules.WHERE filters rows before grouping; HAVING filters groups after it, so HAVING COUNT(*) > 2 works and WHERE COUNT(*) > 2 does not.Window functions (lesson 4) are computed with SELECT, after HAVING, so filtering on one needs a subquery. This is the logical order; theoptimiser may physically do things differently, so long as the answer is the same.

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY: WHERE filters rows before grouping, HAVING filters groups after.

NULL

A comparison with an unknown is itself unknown

customersidnamecity1AshaPune2BenLeeds3ChenNULL4DevLeedsEvery comparison with NULL is unknown, not true or falseNULL = NULLunknownNULL <> 'Leeds'unknownNULL IS NULLtrueCOALESCE(city, 'n/a')'n/a'WHERE city <> 'Leeds'returns Asha only: Chen is droppedWHERE keeps only rows that are trueCOUNT(*) = 4COUNT(city) = 3COUNT(DISTINCT city) = 2NULL means unknown, so a comparison with it is unknown too, and WHERE keeps only what is definitely true.Chen’s city might be Leeds, so “not Leeds” cannot be confirmed and the row is dropped. Write city <> 'Leeds' OR city IS NULL to keep it.The worst trap: x NOT IN (a subquery that returns a NULL) is never true, so the query returns no rows at all. Use NOT EXISTS instead.COUNT(*) counts rows; COUNT(column) counts non-NULL values; SUM and AVG ignore NULLs.

SQL uses three-valued logic: a comparison with NULL is unknown, and WHERE keeps only rows that are true.

Lesson complete

You finished “SQL basics”

Nicely done.

Open in a new tab