set-based-sql

Set-Based SQL

SQL's unit of work is the set, not the row. You describe the result you want with a rule and let the engine produce it "all at once"; you do not walk the data one element at a time. Celko's core test: "ask how you'd specify it in terms of sets, and you'll usually get the right answer." This is the how to think companion to sql-style (how to write) and sql-antipatterns (what to avoid) if installed.

Unlearn the file system

Four habits to drop — they're why procedural SQL is slow and wrong:

• Schemas are not file sets; tables are not files. The whole schema is the unit of work.
• Rows are not records — there is no physical order. ORDER BY is a property of a cursor/result, never of a table. Don't depend on insertion order.
• Columns are not fields — they have types, constraints, and a single meaning, not just a position.

Decision rules

• No cursors / row-by-row loops for set operations. A cursor processes one row at a time and blocks the optimiser from parallelising; an UPDATE / INSERT ... SELECT / MERGE against a set is faster and clearer. Reach for a cursor only for genuinely sequential, non-relational work.
• No app-side loops to do what a join does. Looping in Python/Java to fetch related rows one query at a time is a cursor in disguise — express it as a single set query.
• Push validation into declarative constraints. CHECK, FOREIGN KEY, UNIQUE, DEFAULT, NOT NULL enforce rules once, for every writer — not re-implemented (and forgotten) in each application.
• Replace procedural branching with CASE. A CASE expression is a value-returning, set-based conditional; use it instead of looping with if/then to compute a column.
• Aggregate with the right tool: plain GROUP BY for one grouping; GROUPING SETS / ROLLUP / CUBE for subtotals and crosstabs in one pass instead of UNION-ing many queries.
• Use window (OLAP) functions for running totals, rankings, moving averages, and per-partition calculations — they avoid self-joins and correlated subqueries, and keep detail rows.
• Use set operators — UNION/UNION ALL, INTERSECT, EXCEPT — to combine result sets, rather than procedurally merging them. Prefer UNION ALL when you know rows are already distinct.
• Keep an auxiliary Calendar table and a Numbers/Sequence table. They turn "generate a row per day" or "explode a range" from a loop into a join. Build them once; join to them forever.
• Normalise first. Most "I need a loop" problems are unnormalised designs: a fact represented more than once, or a repeating group that should be rows. Fix the model and the set query falls out (see dimensional-modeling / keys if installed).