Is this the same as JOIN fan-out?

Fan-out is a real key (orders to line items) multiplying rows by items per order — often 2–10×. A missing ON is every row times every row — thousands or millions. Same 'too many rows' smell, different scale and fix.

Why didn't the database error?

CROSS JOIN and comma FROM are legal SQL. Postgres CROSS JOIN customers with no ON is valid. The failure is a timeout, a 500-row cap, or a nonsense total — not a syntax error.

What about ON true or ON 1=1?

Same Cartesian product. Models sometimes write that when they can't find a join key and still want the tables in one query. That's a guess, not a join.

How do I see it without reading the whole SQL?

COUNT(*) of the result vs COUNT(*) of the left table. If the result is about left × right, there is no selective join key. describe_table both sides and name the FK.

Does the 500-row cap hide this?

Yes — you see 500 tidy-looking rows and never the millions underneath. Ask for COUNT(*) of the same join without LIMIT. If that number is enormous, the ON is missing or wrong.

Missing JOIN ON Clause | Accidental CROSS JOIN

You ask for "orders with customer name." The AI writes a join. You get a timeout, a 500-row truncate, or a revenue number with extra zeros. There is no real join key. Every order is paired with every customer.

A join without a key is a multiplication

-- Cartesian product
SELECT o.id, c.name
FROM orders o
JOIN customers c          -- no ON
-- or
FROM orders o, customers c
-- or
FROM orders o
JOIN customers c ON 1 = 1

10,000 orders × 8,000 customers = 80 million rows. Valid SQL. Useless answer. This is not fan-out from line items (a few extra rows per order). Fan-out is "3× too high." This is "thousands of times too high," or the query never finishes (timeout).

The model does this when it hasn't called describe_table, can't find customer_id, and still wants both tables in one SELECT — the same skip as guessing column names.

How to catch it

COUNT(*) FROM orders
COUNT(*) FROM customers
COUNT(*) of your join with no LIMIT
If join ≈ orders × customers, the ON is missing. Show the ON clause.

Also look at distinct order_id vs row count. If every order id repeats once per customer, it's a cross join wearing an inner-join costume.

The fix

SELECT o.id AS order_id, c.id AS customer_id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id

Qualify ids so you don't also hit the wrong-id-after-join bug. If there is no customer_id, say so — don't invent ON 1=1.

The prompt that heads this off

Every JOIN needs an ON that matches a real key (usually table_id).
Never FROM a, b and never ON 1=1. If you can't find the key,
describe_table both sides and ask me — don't cross-join.
After a join, compare COUNT(*) to the left table before summing money.

The short version

No ON (or ON 1=1) is not "a loose join." It is every row times every row. If the result exploded or timed out, read the ON before you raise the timeout.

Read-only MCP gateway: mcpserver.design.

Related reading: