skip to content

Is FROM a CROSS JOIN b WHERE a.id = b.a_id equivalent to FROM a INNER JOIN b ON a.id = b.a_id?

level: middleimportance: should knowfreq 46%

answer

  1. think about how inner join is defined
  2. pair everything, then filter the pairs
  3. CROSS JOIN has no ON clause of its own
  4. identical for inner joins, not for outer ones

basics

~20 s

Yes for inner joins: the standard defines an inner join as the Cartesian product filtered by the join predicate, so both forms return the same rows. CROSS JOIN accepts no ON clause of its own, and the explicit INNER JOIN form reads better.

solid answer

~40 s

They return the same result. Logically, `INNER JOIN ... ON p` is defined as the Cartesian product of the two sources with `p` applied as a filter, so pushing the predicate out to `WHERE` over a `CROSS JOIN` reconstructs it exactly. `CROSS JOIN` itself takes no `ON` or `USING` clause — writing one is a syntax error, because the operation is unconditional by definition. Prefer the explicit `INNER JOIN ... ON` form anyway: it keeps the relating condition next to the tables it relates, separates join conditions from row filters, and turns a forgotten predicate into a parse error instead of a silent explosion. The equivalence is specific to inner joins — with `LEFT`, `RIGHT` or `FULL OUTER` joins, `ON` and `WHERE` are genuinely different, because `ON` runs before NULL-extension and `WHERE` after.

code

sql · 8 lines
sql
-- same result set, two spellings
SELECT e.name, d.dept_name
FROM employees e CROSS JOIN departments d
WHERE e.dept_id = d.dept_id;

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

go deeper

for a junior

Know that an inner join is conceptually every pairing filtered by the condition, and that CROSS JOIN is the same operation with the filtering step left to you.

for a middle

Explain the equivalence from the definition, state that CROSS JOIN rejects ON syntactically, and give the readability and safety reasons the explicit INNER JOIN ... ON form is preferred.

for a senior

Be precise about where the equivalence ends: outer joins treat ON and WHERE differently, and no filter over a Cartesian product can produce NULL-extended rows.

for a principal

Own the convention — explicit join syntax everywhere, CROSS JOIN reserved for products that are genuinely intended, so a Cartesian product in a diff is always a deliberate signal.

## The definition behind the equivalence SQL defines an inner join in terms of the Cartesian product: take every pairing of a left row with a right row, then keep the pairings for which the join condition evaluates to true. That is literally what `a CROSS JOIN b WHERE a.id = b.a_id` spells out step by step, so the two queries below are the same query written two ways: ```sql SELECT * FROM employees e CROSS JOIN departments d WHERE e.dept_id = d.dept_id; SELECT * FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id; ``` Same rows, same columns, same cardinality. Nothing obliges an engine to build the intermediate product to answer the first form; the two are recognised as the same logical operation. ## CROSS JOIN takes no ON clause The grammar allows a join condition only on the conditional join types — `INNER`, `LEFT`, `RIGHT`, `FULL`. `CROSS JOIN` is the unconditional one, so `a CROSS JOIN b ON a.id = b.a_id` does not parse. Likewise `USING (id)` is not available on a cross join. If you find yourself wanting to write one, what you actually want is `INNER JOIN ... ON`. The converse spelling exists too: `a INNER JOIN b ON TRUE` (or `ON 1 = 1`) is a conditional join with an always-true predicate, which is the Cartesian product again. It shows up when a syntax position requires an `ON` clause, but `CROSS JOIN` says the same thing more clearly. ## Why the explicit form is still preferred If the results are identical, the argument is entirely about readability and safety: - **Relationships stay next to the tables.** `ON` binds the condition to the join it belongs to, so a reader scanning a five-table query can see how each table hangs off the previous one without parsing a long `WHERE` clause. - **Join conditions and row filters stop competing for space.** `WHERE` then contains only genuine filters — `e.hired_on > DATE '2020-01-01'` — instead of a mix in which one missing conjunct is invisible. - **A missing predicate becomes a syntax error.** `FROM a JOIN b` without `ON` will not parse. `FROM a CROSS JOIN b` (or `FROM a, b`) with the `WHERE` conjunct forgotten runs perfectly and returns a Cartesian product. Turning a silent semantic bug into a parse error is the strongest practical reason for the explicit form. - **Intent is documented.** Reserving `CROSS JOIN` for products you actually want means its appearance in a diff is a signal rather than noise. ## Where the equivalence stops The interchangeability of `ON` and `WHERE` is a property of *inner* joins only. For outer joins the two clauses run at different points: the `ON` condition decides which pairings match, and unmatched preserved-side rows are then NULL-extended; a `WHERE` predicate runs afterwards on the joined result. A condition on the optional side placed in `WHERE` therefore rejects the NULL-extended rows and collapses the outer join to an inner one. So `a LEFT JOIN b ON a.id = b.a_id AND b.status = 'X'` and `a LEFT JOIN b ON a.id = b.a_id WHERE b.status = 'X'` are different queries with different row counts — a distinction that has no analogue in the inner-join case. A second boundary: `CROSS JOIN` cannot express an outer join at all. There is no way to filter a product down to "matched pairs plus unmatched left rows extended with NULLs", because the product contains no NULL-extended rows to preserve. Outer joins add rows that the Cartesian product never had. ## Practical reading When you meet `CROSS JOIN` plus a relating `WHERE` predicate in existing code, read it as an inner join written the long way and, if you touch the query, rewrite it. When you meet a comma-separated `FROM` list with the predicates in `WHERE`, the same applies. When you meet a bare `CROSS JOIN` with no relating predicate anywhere, decide whether the product is intentional — a spine, a combination matrix, a one-row summary attached to every row — or a bug.

  • Does the same interchangeability of ON and WHERE hold for a LEFT JOIN?
    No. `ON` decides which pairings match before unmatched left rows are NULL-extended; `WHERE` filters the joined result afterwards. A predicate on the right-hand table placed in `WHERE` is never true for a NULL-extended row, so it discards exactly the unmatched rows the outer join preserved, collapsing it to an inner join.
  • Can CROSS JOIN plus a WHERE predicate reproduce a FULL OUTER JOIN?
    No. Filtering a Cartesian product can only ever remove pairings, and the product contains no NULL-extended rows. An outer join *adds* rows that the product never held — a left row with all right columns NULL. No `WHERE` predicate over a cross join can conjure them.
  • What does INNER JOIN b ON TRUE mean, and when would you write it?
    It is a conditional join whose predicate is always satisfied, so it produces the Cartesian product — the same result as `CROSS JOIN b`. It appears where a syntax position demands an `ON` clause, most often when attaching an optional derived table with `LEFT JOIN ... ON TRUE`. For a plain product, `CROSS JOIN` states the intent more clearly.

saying these in an interview costs you the question

  • Says CROSS JOIN accepts an ON clause like other joins
  • Claims the two forms return different row counts
  • Generalises the ON/WHERE equivalence to outer joins
  • Thinks CROSS JOIN plus WHERE can emulate a LEFT JOIN
  • Argues the comma form is always wrong regardless of intent

context

resolving links…