sql-reviewlisted

# sql-review The dangerous SQL is the SQL that runs fine in dev and wrong in prod: a DELETE that matches every row, a JOIN that fans out, a migration that locks a table for ten minutes. This skill catches those before they ship. ## When to use Writing a non-trivial query, reviewing a migration, or about to run a write statement against real data. ## Before any UPDATE or DELETE Run the WHERE clause as a SELECT first. The count it returns is the number of rows you are about to change. ```sql -- Don't run this yet: DELETE FROM orders WHERE status = 'cancelld'; -- typo: matches 0 rows, or the wrong ones -- Run this first: SELECT count(*) FROM orders WHERE status = 'cancelld'; ``` If the count surprises you, stop. The count is a sanity check, not a lock — rows can change between the SELECT and the write. The transaction is what lets you back out: ```sql BEGIN; DELETE FROM orders WHERE status = 'cancelled'; -- read the row count it reports, then: COMMIT; -- or ROLLBACK; if it's wrong ``` ## Correctness - **NULL is not a value.** `col = NULL` is never true — use `IS NULL`. `NOT IN (subquery)` returns nothing if the subquery yields a single NULL. - **JOIN fan-out.** A one-to-many JOIN multiplies rows, and now your `SUM` is wrong. Check the grain before you aggregate. - **GROUP BY.** Every non-aggregated column in the SELECT must be in the GROUP BY. - **Implicit casts.** Comparing a string column to a number can scan the whole table or match nothing. ## Performance ``