AI-generated SQL gets joins wrong because the model has to guess your schema, not because it is bad at SQL. The fix is schema linking: retrieving the relevant tables, foreign keys and column values from the live database and putting them in front of the model before it writes anything.
Ask any current model for "revenue by region last quarter" with no context and it will produce clean, idiomatic, completely fictional SQL — a `revenue` column, a `regions` table, a join on `region_id`. None of it exists. The SQL is not the problem; the grammar is small and every model has it cold. Your schema is the problem, and nothing was trained on it.
The three ways a join goes wrong
Almost every incorrect generated query fails in one of three ways, and all three are schema knowledge rather than SQL skill.
- The invented column. `customers.revenue` looks reasonable and does not exist; the money is in `orders.total_cents`.
- The wrong join path. Two tables genuinely relate, but through a third — and the model joins them directly on a column with a matching name and a different meaning.
- The fan-out. The join is valid and produces four rows where there should be one, because a one-to-many was treated as one-to-one. This is the dangerous one: it returns a plausible number that is exactly four times too large.
The third is worth dwelling on. The first two produce errors or obviously empty results and you catch them. A fan-out produces a number, and numbers get pasted into decks.
Why pasting the schema into the prompt stops working
The obvious fix is to give the model the schema, and it works beautifully on the sample database in the tutorial. It fails on real ones for two independent reasons.
- It does not fit. A two-thousand-table warehouse's DDL is far past any context window, and truncating it arbitrarily is worse than not including it.
- Even where it fits, it hurts. Burying the eight relevant tables among two thousand irrelevant ones measurably degrades selection. More context is not more grounding — the signal-to-noise ratio is what matters, and dumping the schema minimises it.
Schema linking is a retrieval problem
The right framing is that the model does not need your schema; it needs the eight tables the question is about. That is retrieval, and retrieval is a mature technology. Introspect the catalog once, index it, and search it per question.
What has to be in the index
Table and column names alone are not enough — they get you past the invented column and leave both join failures intact.
- Foreign keys. These are the join paths. With them, the path between two tables is looked up; without them, it is guessed from name similarity, which is exactly failure mode two.
- Cardinality. Knowing a relationship is one-to-many is what prevents the fan-out, and it is derivable from the key definitions and the statistics.
- Distinct values on low-cardinality columns. So a filter on status matches `'active'` rather than `'ACTIVE'` or `3`.
- Table statistics. Rough row counts change which query shape is correct, not just which is faster.
Execution is the check that catches the rest
Retrieval makes the query much more likely to be right. Running it is how you find out. A one-shot system hands you SQL and the verification is your problem; a system that executes its own query read-only can see that a question about thousands of customers returned zero rows, or that a total is suspiciously round, and revise before showing you anything.
This is only a reasonable design if the generated SQL cannot do damage. SQLore parses every statement to an AST and validates it down to a single read-only SELECT before execution — no writes, no DDL, no stacked statements — so the self-correction loop costs nothing but a little latency.
The part you still have to supply
Retrieval cannot recover meaning that is not in the database. If "active" means `status_id = 3` in your system, that fact exists in a Slack thread from 2023 and nowhere in the catalog. Every serious text-to-SQL setup needs a way to write those down once — and the number of them is small, finite, and worth an afternoon.