Rows in the Drawer

SQL on a made-up pathology lab, one clause at a time

Lab data comes nested: a case holds parts, a part holds blocks, a block gives slides, and a slide can be scanned more than once. A lot of SQL mistakes happen right at those joins. This page runs six small queries over a tiny made-up lab, one clause at a time, so you can watch the rows (drawn as index cards) get copied, crossed out, piled up, and numbered. Each query comes in two versions that differ by a keyword or a line. Version A is the one often written first; version B is the fix (or, in the last query, the other way to count).

The drawers

Three tables, small enough to read card by card. Open a drawer to see what is in it.

A slide label like A1-2 on case S26-101 reads: part A, block 1, slide 2 cut from that block. A real lab system usually keeps parts and blocks as their own records; here they are folded into the label to keep the drawers small. Labs format their labels in their own ways.

A few words used below: NULL means no value recorded. A CTE (the WITH … AS part) is a named sub-query written first. LAG reads a value from the previous row. H&E and IHC are kinds of stain; QC is quality control.

Three details are planted on purpose, because real data has them: slide 1 and slide 5 were rescanned, after a blurred scan and a scan that cut off part of the tissue; scan 7's barcode did not read, so it has no slide_id; and slide 7 has not been scanned yet, while slide 8's scan is still waiting on QC.

Six queries, clause by clause

A database reads a query in a different order than it is written. FROM and the joins come first, then WHERE, GROUP BY, HAVING (none of the six needs it), window functions, SELECT, and ORDER BY last. Window functions are really worked out as part of SELECT, after HAVING; this page gives them their own step so you can watch them. Pick a query, pick a version, and step through. The highlighted part of the SQL is the clause doing the work.

The arrow keys step back and forward while the stepper has focus. These steps are the logical order, the order a query's meaning is defined in. A real database plans its own physical order and may filter, join, or sort in a different sequence, as long as the answer comes out the same.

What the six show

The join that doubled. Joining a parent to two child tables copies the parent once per child row. Counting after that counts copies. COUNT(DISTINCT …) is one fix; counting each child table in its own subquery before joining is another.
The LEFT JOIN that went missing. A LEFT JOIN keeps unmatched rows with NULLs, and a WHERE test such as = on the right-hand table then throws them away, because NULL = 'pass' is not TRUE. Moving the test into ON keeps them.
NOT IN and the unread barcode. x NOT IN (…) is never TRUE if the list holds a NULL, so the query quietly returns nothing. NOT EXISTS does not have this problem.
The latest rescan. Window functions are worked out after WHERE, so they cannot be filtered there. Compute the row number in a CTE or subquery and filter outside it.
The previous scan. Leaving out PARTITION BY makes LAG reach back across scanners, so a scan that follows the other scanner's work borrows its time (scans 4, 7 and 10 here).
Piles or notes in the margin. GROUP BY turns each group into one row. A window function writes the group's total on every row and keeps them all.

Scratchpad

Try your own queries against the same three tables. The scratchpad runs SQLite inside this page using sql.js, SQLite compiled to WebAssembly. It is fetched from the jsDelivr CDN only when you ask for it (well under a megabyte), and nothing you type leaves the page. SQLite is one dialect among several, so a few functions are spelled differently elsewhere.

SQLite is not loaded yet.

Query

Open a .sql file

The query box is kept in this browser between visits. The download is a plain SQL script that sets up the tables in any SQLite, with the query at the end.

Already out there

SQL Visualizer from DPriver turns a pasted query into a diagram laid out in execution order, from sources through joins, filters, aggregates, and window functions.
viSQLizer was a research prototype that broke queries into animated steps for students; a 2016 study found it helped about as much as an online tutorial.
sql-practice.com sets graded practice questions on a small hospital database of patients, admissions, and doctors.

This page tries something narrower: a few known mistakes, on nested lab-style data, with every row shown at every clause and the fix one click away.

Sources

Logical processing order of SELECT, and that the physical order can differ: Microsoft Learn, SELECT (Transact-SQL).
Window functions run after WHERE, GROUP BY, and HAVING, and are not allowed in them; filter in a sub-select instead: PostgreSQL documentation, Window Functions tutorial.
SQLite's window functions, including ROW_NUMBER and LAG, added in version 3.25.0: SQLite window functions.
Why NOT IN against a list with a NULL filters every row: a worked example on Microsoft Q&A, and the jOOQ manual's note on NOT IN, which suggests NOT EXISTS. Why a WHERE test can undo a LEFT JOIN: jOOQ blog, The difference between SQL's JOIN .. ON clause and the WHERE clause.
Join fan-out and its fixes: Google Cloud community, The problem of SQL fanouts.
Case (accession), part, block, and slide as the usual levels of a surgical pathology specimen: DICOM PS3.17, Annex NN, Specimen Identification and Management; LibrePathology, Anatomical pathology laboratory process. Labels like S14-9999 A1: College of American Pathologists, Uniform Labeling of Blocks and Slides in Surgical Pathology.
sql.js: project page.

The lab, its cases, patients, and scanners are made up. Every result the stepper shows was checked against SQLite running the same SQL on the same tables.