Write SQL from Plain English — Then Prove It Right

Tier: Foundation · Workflow area: SQL Authoring & Query Tuning · ~25 minutes

This playbook turns natural-language-to-SQL from a party trick into a professional workflow. The AI drafts; you prove. The proof is three cheap checks — cardinality sanity, edge-case probes, and a plan read — that together catch the join fan-outs, NULL traps and silent filters that make generated SQL dangerous.

When to use

• Unfamiliar schema, familiar questionyou know exactly what the business wants ("revenue by region, net of refunds, last quarter") but the schema is new to you. AI plus proof beats an hour of schema spelunking.
• Boilerplate-heavy queriespivots, window-function rankings, gaps-and-islands, date bucketing. The AI writes the scaffolding in seconds; your review time goes into correctness, not syntax recall.
• Teaching momentsa junior analyst keeps writing correlated subqueries by hand. Pairing them with this loop teaches both the SQL idiom and the verification habit at once.

When NOT to use

• Queries that feed money movement or compliance filings unreviewedanything whose output drives payments, regulatory reports or access decisions gets a second human reviewer, not just this checklist.
• When you cannot share the schemaif data-classification rules bar even table structures from an external assistant, use a tenant-approved model or write the SQL yourself. Structure can leak business strategy.
• Sub-second lookups you could type fastera single-table WHERE id = ? needs no assistant. Save the loop for queries with joins, aggregates or windows, where the failure modes hide.

The recipe

1. 1.

   Feed real structure, not a description from memory. Dump the actual DDL for the tables involved — misremembered column names are the top cause of subtly wrong generated SQL:

   Copy

   psql -d appdb -c '\d+ orders' -c '\d+ order_lines' -c '\d+ customers'
   

   Or pull it programmatically for a cleaner paste:

   Copy

   SELECT table_name, column_name, data_type, is_nullable
   FROM information_schema.columns
   WHERE table_name IN ('orders', 'order_lines', 'customers')
   ORDER BY table_name, ordinal_position;
   

2. 2.

   Ask with constraints, definitions and an escape hatch. Vague questions produce confident guesses. A production-grade prompt pins down grain, filters and dialect:

   Copy

   You are writing PostgreSQL 16 SQL. Schema (actual DDL) below.
   
   Question: monthly net revenue by customer region for the last 6 full
   calendar months. Net = order_lines.amount minus refunds
   (orders.status = 'refunded' means the whole order is refunded).
   Exclude test customers (customers.is_test = true).
   Grain: one row per region per month. Currency is uniform.
   
   Rules:
   - Use explicit JOINs and a CTE per logical step, commented.
   - State every assumption you make as a SQL comment at the top.
   - If the schema cannot answer the question as asked, say so
     instead of writing your best guess.
   
   [paste DDL]
   

   The "state every assumption" line is the highest-value sentence: it surfaces the model's guesses (how it treated NULL regions, which date column it used) where you can read them.

3. 3.

   Sanity-check cardinality before you read a single value. Fan-out from a missed join condition is the classic silent failure. Compare the result's implied row count against ground truth:

   Copy

   -- Ground truth: how many distinct region-month pairs should exist?
   SELECT COUNT(*) FROM (
     SELECT DISTINCT c.region, date_trunc('month', o.ordered_at)
     FROM orders o JOIN customers c ON c.id = o.customer_id
     WHERE o.ordered_at >= date_trunc('month', now()) - interval '6 months'
   ) t;
   
   -- And: does the generated query's total reconcile to a trivial sum?
   SELECT sum(amount) FROM order_lines ol
   JOIN orders o ON o.id = ol.order_id
   WHERE o.ordered_at >= date_trunc('month', now()) - interval '6 months';
   

   If the generated query's grand total does not reconcile to the trivial sum minus refunds, stop. A good check output looks like: trivial sum 4,812,330.50 − refunded 213,004.10 = 4,599,326.40; query total 4,599,326.40 ✓.

4. 4.

   Probe the edges the model glossed over. Three probes catch most generated-SQL bugs:

   Copy

   -- NULL regions: did they vanish, or land in their own row?
   SELECT count(*) FROM customers WHERE region IS NULL AND NOT is_test;
   
   -- Boundary months: is the "last 6 full months" window off by one?
   SELECT min(ordered_at), max(ordered_at) FROM orders
   WHERE ordered_at >= date_trunc('month', now()) - interval '6 months';
   
   -- Duplicates at the declared grain: must return zero rows
   SELECT region, month, count(*) FROM (/* generated query */) q
   GROUP BY region, month HAVING count(*) > 1;
   

5. 5.

   Read the plan — for correctness clues, not just speed. EXPLAIN tells you what the query actually does:

   Copy

   EXPLAIN (ANALYZE, BUFFERS) /* generated query */;
   

   Look for a join producing far more rows than its inputs (fan-out), a filter row count of zero on a predicate you expected to bite (wrong column or type mismatch), and seq scans on large tables you will care about at production scale.

6. 6.

   Record the verdict with the query. Wherever the SQL lands — a dashboard, a report repo, a ticket — attach two lines: reconciled against trivial sum on 2026-08-06; NULL-region and boundary probes passed. Future-you inherits verified SQL, not folklore.

Make it yours

• MySQL 8tell the model the version explicitly (CTEs and window functions need 8.0+), swap date_trunc idioms for DATE_FORMAT(ordered_at, '%Y-%m-01'), and use EXPLAIN ANALYZE (8.0.18+) for the plan read.
• SQL Serverrequest T-SQL with DATETRUNC (2022+) or EODATE idioms per your version, and read the actual execution plan in SSMS; the cardinality sanity check translates unchanged. SSMS Copilot can draft, but the proof loop stays yours.
• Oracleask for Oracle-dialect SQL with TRUNC(ordered_at, 'MM'), watch the empty-string-equals-NULL semantics in any string filters, and verify with EXPLAIN PLAN FOR plus DBMS_XPLAN.DISPLAY.

Quality check

• Every assumption the model stated has been readthe comment block from step 2 is the review surface. An unread assumption is an unverified guess in production.
• The reconciliation check used an independent pathproving a query against itself proves nothing. The trivial-sum comparison must be simpler than the query it checks.
• Probes ran against real data, not the model's claimsnever accept "this query handles NULLs correctly" as text. Only result sets count as evidence.
• Run on a replica or with a row limit firsta generated query with a missing join predicate can be a cross-join bomb. First execution happens where a runaway query cannot hurt.