GitHub Copilot for SQL

Every other language in this cluster fails loudly when it is wrong. A type error, an exception, a panic, a non-zero exit. SQL does not.

A generated query almost always runs. It almost always returns rows. The rows almost always look reasonable. And it is entirely possible for all three to be true while the numbers are wrong — because a join fanned out, because a NULL silently excluded rows from a comparison, or because refunded orders were never filtered out. Nobody gets an error. The number goes into a report.

That is what makes SQL the right lesson to end this cluster on. It is the language where “it ran and looked fine” is furthest from evidence.

Schema is the context

In TypeScript, the type definitions are the best prompt available. SQL’s equivalent is the schema, and unlike a type definition it is not in a file the model can see.

So paste it. The CREATE TABLE statements for the tables involved, including constraints, indexes and — this is the part people omit — a sentence about what the status columns actually mean.

The two sentences of semantics are the important part. “Only ‘paid’ counts as revenue” and “order_items is one-to-many with orders” are facts no schema dump carries, and they are precisely the two facts the query below gets wrong without them.

Practical example: an analytics query that was wrong

CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,
    email         TEXT    NOT NULL UNIQUE,
    full_name     TEXT    NOT NULL,
    created_at    TEXT    NOT NULL
);

CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER NOT NULL REFERENCES customers(customer_id),
    placed_at     TEXT    NOT NULL,
    status        TEXT    NOT NULL,   -- 'paid' | 'refunded' | 'cancelled'
    discount      NUMERIC              -- nullable on purpose
);

CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY,
    order_id      INTEGER NOT NULL REFERENCES orders(order_id),
    product_id    INTEGER NOT NULL REFERENCES products(product_id),
    quantity      INTEGER NOT NULL,
    unit_price    NUMERIC NOT NULL
);

CREATE INDEX idx_orders_customer_placed ON orders(customer_id, placed_at);
CREATE INDEX idx_order_items_order ON order_items(order_id);

SELECT c.customer_id, c.full_name,
       SUM(oi.quantity * oi.unit_price) AS total
FROM customers c
JOIN orders o        ON o.customer_id = c.customer_id
JOIN order_items oi  ON oi.order_id   = o.order_id
LEFT JOIN products p ON p.product_id  = oi.product_id
WHERE o.placed_at >= date('now', '-30 day')
GROUP BY c.customer_id, c.full_name
ORDER BY total DESC
LIMIT 10;

WITH order_totals AS (
    SELECT o.order_id,
           o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.placed_at >= date('now', '-30 day')
      AND o.status = 'paid'
    GROUP BY o.order_id, o.customer_id
)
SELECT c.customer_id,
       c.full_name,
       COUNT(t.order_id)            AS orders,
       ROUND(SUM(t.order_total), 2) AS total_value
FROM order_totals t
JOIN customers c ON c.customer_id = t.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY total_value DESC, c.customer_id
LIMIT 10;

First draft                    Corrected
Grace Example    1280          Grace Example     420
Linus Example     510          (absent)
Alan  Example     390          Alan  Example     390
Margaret          370          Margaret          370

SELECT c.full_name,
       COUNT(o.order_id)          AS orders_naive,
       COUNT(DISTINCT o.order_id) AS orders_correct
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
GROUP BY c.customer_id, c.full_name
ORDER BY orders_naive DESC LIMIT 4;

Grace Example        16    5
Alan Example         12    3
Katherine Example    11    4
Linus Example         9    3

rows total                        : 31
discount IS NULL                  : 31
WHERE discount <> 5.00            :  0     <- drops every row
WHERE discount IS NULL OR <> 5.00 : 31

order_id  discount  gross  gross - discount   gross - COALESCE(discount, 0)
1         5.00      60     55                 55
4         NULL      100    NULL               100

input: x@example.test' OR '1'='1

string-built query  : 12 rows   <- the entire customers table
parameterised query :  0 rows   <- one literal value, matched nothing

Dialects: there is no single SQL

Copilot will produce a dialect. Which one depends on your context, and if the context is thin it will be whichever is most common in public code for that shape of query. Name your engine and version in every prompt.

The differences that bite most often:

Beyond syntax, semantics differ too. MySQL’s default collation is case-insensitive and PostgreSQL’s is not, so the same WHERE email = ? matches different rows. GROUP BY in MySQL historically permitted selecting non-aggregated columns; PostgreSQL never has. COALESCE is the portable choice in every row of that table, and worth preferring for that reason alone.

Query construction: joins, CTEs and window functions

Joins. The two questions for every join in a generated query: is it needed, and does it change the row count. An unused join is a sign nobody checked. A join to a one-to-many table before an aggregate is the fan-out above. LEFT JOIN followed by a WHERE on the right table’s column silently converts it back to an inner join — a genuinely common defect, and one that reads as correct.

CTEs. Copilot writes good CTEs, and they are worth asking for by name. A query broken into named stages is reviewable stage by stage: you can run the CTE alone and check its row count before trusting what is built on it. That is the closest thing SQL has to unit testing.

Window functions. Reliable in generated code and frequently the right answer where a self-join was suggested. ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) for deduplication, SUM(...) OVER () for a share-of-total, LAG and LEAD for period-over-period. The thing to check is the frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and the default RANGE frame differ when the ordering column has ties, and generated code rarely specifies one.

Subqueries. A correlated subquery in a SELECT list runs once per row. Often fine, occasionally catastrophic. If a generated query has one over a large table, check whether a join or a window function replaces it.

Reading a plan, and what “slow” means

EXPLAIN shows the plan; EXPLAIN ANALYZE runs the query and shows what actually happened. The second is the one that answers questions, and it is safe on a SELECT. On an UPDATE or DELETE, EXPLAIN ANALYZE executes the statement — wrap it in a transaction you roll back.

What to look for in a generated query’s plan:

• A sequential scan on a large table where an index exists. Usually a predicate that is not sargable: a function applied to the column (WHERE date(placed_at) = …) rather than to the value.
• A row-count estimate far from reality. Statistics are stale, or the predicate is more selective than the planner believes.
• A nested loop over a large outer relation, which is the shape of an accidental cross join.

Asking Copilot to explain a plan is a genuinely good use of chat — plan output is dense, structured and precise, which is the kind of context the model handles well. Asking it to optimise a query without the plan and the schema is asking it to guess.

Explaining and debugging queries

Reading SQL is where Copilot is at its most reliable in this lesson, and it is worth using deliberately rather than only when stuck.

Explaining an inherited query. Analytics codebases accumulate long queries that nobody currently on the team wrote — six CTEs, a window function and a case expression covering a business rule that changed twice. Pasting the query and the schema into chat and asking for a stage-by-stage explanation is fast and accurate, because it is a reading task over precise input. Ask specifically what grain each stage produces; that is the question whose answer catches the fan-out problem.

Explaining a discrepancy. “This query returns 1280 and the finance report says 420 — what could differ?” is a genuinely good prompt, because it is asking for hypotheses rather than for an answer. The list it produces — a status filter, a date boundary, a join multiplying rows, a currency conversion, a timezone — is a checklist you then verify yourself. That is a better division of labour than asking it to fix the query.

Debugging by decomposition. The mechanical technique that works regardless of tooling: run each CTE alone and check its row count against what you expect. Where the count first surprises you is where the defect is. A query written as one nested expression cannot be debugged this way, which is the practical reason to ask for CTEs rather than subqueries.

Comparing two queries. When you have rewritten a generated query, do not assume the rewrite is equivalent. Run both, compare row counts, then compare the full result sets with an EXCEPT in both directions — a query that returns the same number of rows with different contents is a failure mode that eyeballing the first ten rows will not find.

One caution on generated explanations: a model reading a query tells you what the SQL says, not what the data contains. It cannot know that status has a fourth value in production that nobody documented, or that a nullable column is never actually null. Those are questions for the database, and the answer is a quick SELECT DISTINCT rather than a prompt.

Migrations and destructive statements

This is where SQL earns its place at the end of this cluster.

A generated SELECT that is wrong wastes your time. A generated UPDATE or DELETE that is wrong destroys data, and unlike every other language here there is no local test run that would have caught it — the data only exists in one place.

Two more migration-specific points. Generated migrations frequently omit the reverse operation entirely, or write one that does not actually reverse the change — dropping a column the up-migration added is not the same as restoring the data it held. And a suggested ALTER TABLE will not mention that it takes an exclusive lock; on a large table in production that is an outage, and the engine-specific safe pattern is something you have to ask for.

Testing SQL

SQL is under-tested for the same reason shell is: it does not feel like code. It is, and generated SQL especially warrants it.

• Assert on a known dataset. A fixture with hand-computed expected totals is what catches the fan-out above. It is the only thing that catches it.
• Test the boundaries. No rows, one row, a NULL in every nullable column, a duplicate where you expected uniqueness, a value exactly on the date cut-off.
• Test the migration both ways against a copy of production-shaped data.
• pgTAP for in-database assertions, dbt tests for analytics models, and ordinary application tests for query results — whichever fits, the point is that a query with no test has been verified by nobody.

Best practices

• Paste the schema, and add the sentence about what the status column means.
• Name the engine and version in every prompt.
• Ask for CTEs so the query can be checked stage by stage.
• Aggregate at an explicit grain before joining anything else.
• COALESCE every nullable column that reaches arithmetic or a comparison.
• Parameterise, always. Validate identifiers against an allow-list.
• Never run generated UPDATE or DELETE without seeing the SELECT first.

Common mistakes

• Trusting a query because it returned plausible numbers.
• Aggregating across a one-to-many join and inheriting the multiplication.
• Forgetting the status filter, so cancelled and refunded rows count as revenue.
• Writing <> or NOT IN against a nullable column.
• Accepting an ORM’s raw query helper because it looks like the safe one.

Cluster 3 complete

That is thirteen lessons and twelve languages. If there is one idea to carry out of the cluster, it is the one this lesson demonstrates most sharply: the question is never how good Copilot is at your language, it is what your language lets you check. Rust’s borrow checker, TypeScript’s compiler, ShellCheck, PHPStan, EXPLAIN — each is a way of turning a judgement call into a mechanical one, and each is worth setting up before you accept suggestions in volume.

Next in the Academy is Cluster 4 — Copilot for DevOps and Infrastructure: Dockerfiles, Compose, Kubernetes manifests, Helm, Terraform, Ansible, GitHub Actions and incident workflows. Three lessons here are its direct foundation — Bash for the review discipline infrastructure demands, Python for automation, and Go because most of the infrastructure you will be automating is written in it. This lesson matters there too: infrastructure state lives in a database somewhere, and a migration is the one deploy artefact that cannot simply be rolled back.