CROSS JOIN

Definition

CROSS JOIN explicitly requests a Cartesian Product (7.7) — every row of one table paired with every row of the other, with no matching condition at all.

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

sql
SELECT e.name, d.name AS department
FROM employees e
CROSS JOIN departments d;

With 4 employees and 3 departments, this produces 12 rows — every employee paired with every department, regardless of who actually works where. CROSS JOIN is rarely what you want as a FINAL answer, but it's a genuinely useful building block for generating combinations: e.g. a calendar table crossed with a list of stores to produce "every store, every day" as a base for a reporting query, before joining in actual sales data.

Edge Cases and Pitfalls

• Writing FROM a, b (comma-separated, old-style syntax) with NO WHERE condition also produces a CROSS JOIN — this is exactly the "accidentally forgot the join condition" bug from 7.7, now seen in its literal SQL form; explicit CROSS JOIN syntax makes the intent to produce every combination clear and deliberate, rather than an oversight.
• CROSS JOIN needs no ON clause at all (there's nothing to match) — adding one is a syntax error for a true CROSS JOIN; if you find yourself wanting an ON condition, you actually want INNER JOIN.
• Row-count explosion is a real, immediate risk: crossing two even moderately-sized tables (1,000 x 1,000) produces a million rows — always double check this is genuinely intended before running on large tables.

Key Takeaways

• CROSS JOIN explicitly produces every row-combination between two tables — no matching condition at all.
• Old-style comma-separated FROM with no WHERE is an implicit, easy-to-miss CROSS JOIN.
• Genuinely useful for deliberately generating combinations (e.g. dates x stores), but a row-count risk on large tables.