LLM-Written Joins: How Orders Go Missing or Count Twice

A join changes how many rows you have, and the SUMs and COUNTs after it inherit the change. An inner join discards rows that find no match, and a join to a child table returns the parent once per child, so orders vanish or repeat with no error message. Joins are one of the error types that studies have quantified in model-written SQL: the Spider 2.0 authors put them at 8.3% of the errors in a 300-example review, and no study cited here measures them on Shopify or GA4 orders. Count rows, distinct orders and revenue before and after the join to see which happened.

What do the studies and documentation show?

Three peer-reviewed papers looked at model-written joins. All used public benchmarks.

• Spider 2.0 (ICLR 2025 Oral) reviewed the errors of its two code-agent frameworks on 300 randomly sampled examples. Joins were 8.3% of the errors: BigQuery databases "often lack explicit foreign key", so models must "infer potential keys based on column names and descriptions".
• Shen et al. (FSE 2026) read 4602 wrong queries from four text-to-SQL systems, run with GPT-3.5 and GPT-4o on the BIRD and Spider development sets. They name comparing unrelated columns, for example two that share a name, as an error "common in the JOIN clause generated by LLMs", and say it gives "either an empty set or a joined table with irrational relationship between columns".
• NL2SQL-BUGs (KDD 2025) catalogues semantic errors in SQL written for BIRD questions. Its worked example of a join-type error: for a question asking for students "including students who haven't taken any courses", the incorrect SQL uses an INNER JOIN, which "excludes students without courses".

The evidence is strong on mechanism and weaker on rates: public benchmarks, 2024-era models, and join figures that are shares of errors, not failure rates per query. Models also improve: under the same techniques, Shen et al. report that GPT-4o made 48.7-80.4% fewer semantic errors than GPT-3.5. No study cited here measures how often an LLM-written join drops or repeats orders in Shopify or GA4 data.

What does a join do to the number of rows?

PostgreSQL's reference says that for an inner join, "for each row R1 of T1, the joined table has a row for each row in T2 that satisfies the join condition with R1." That is a repeat when R1 has several matches and a drop when it has none. BigQuery says an inner join "discards all rows that don't meet the join condition." A left join fixes the drop only: PostgreSQL says the result "always has at least one row for each row in T1", and at least one is not exactly one.

Shopify and GA4 data have both shapes. Shopify's API describes an order's line items as the individual products and quantities that make up the order, so joining orders to line items repeats each order-level figure once per line. GA4's export schema says the items record "is repeated for each item". For illustration: an order worth 90 with three line items becomes three rows, and SUM over those rows returns 270. Drops come from IDs that don't match. Shopify documents order names with a leading hash, a letter prefix or a letter suffix, so an ID that GA4 holds as plain digits does not equal the order name.

How do I check a join on my own data?

Count three things on the source table (one row per order), then the same three on the joined rows before any GROUP BY.

-- 1. The source: orders for the period
SELECT COUNT(*)                 AS row_count,
       COUNT(DISTINCT order_id) AS orders,
       SUM(order_total)         AS revenue
FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';

-- 2. The same three numbers after your join, before any GROUP BY
SELECT COUNT(*)                   AS row_count,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(o.order_total)         AS revenue
FROM orders o
JOIN order_channels c ON c.order_id = o.order_id
WHERE o.created_at >= '2026-09-01' AND o.created_at < '2026-10-01';

On the GA4 export, run the purchase query once as it is and once with , UNNEST(items) AS item added to the FROM clause. Revenue moves when items repeats the purchase.

SELECT COUNT(*) AS row_count,
       COUNT(DISTINCT ecommerce.transaction_id) AS orders,
       SUM(ecommerce.purchase_revenue) AS revenue
FROM `your_project.analytics_123456789.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20260901' AND '20260927';

Read the rows like this:

1. Pass. Row count equals distinct orders, distinct orders match the source, and revenue matches the source. A difference you can explain, such as a filtered test order, is fine. One you cannot explain is a fail.
2. Rows above distinct orders. A parent row is repeated. Collapse the child table to one row per order first, then join.
3. Distinct orders below the source. Rows were dropped. Start from your full order list, left join the other table and keep the rows where its key is NULL. Look for an ID written differently or a filter inside the join condition.
4. Row count matches but revenue does not. One order was dropped and another repeated, so the count cancelled out. Compare all three numbers.

Finish by comparing the source total with Shopify's orders and total sales for the same dates. If the gap looks like a day of orders, read the time zone and currency post before you blame the join.

An LLM can draft a join and explain each clause in plain words. The three numbers are what let you keep its draft.

Sources, 30 September 2026: Spider 2.0 paper (Lei et al., ICLR 2025); ICL-based text-to-SQL error study (Shen et al., FSE 2026); NL2SQL-BUGs (Liu et al., KDD 2025); PostgreSQL, Joined Tables (PostgreSQL Global Development Group, 2026); BigQuery query syntax (Google Cloud, 2026); GA4 BigQuery Export schema (Google Analytics Help, 2026); Shopify Admin API, Order (Shopify, 2026).

Related answers

• Why an average of averages is not the total
• Counting the same order twice in GA4
• Time zones and currencies in order totals
• Causal attribution