CROSS JOIN

Joins
Buy Me A Coffee

Produces the cartesian product of two tables — every row of the left combined with every row of the right. No join condition.

Produces a Cartesian product: every row in the first table paired with every row in the second. Can be extremely large and expensive. Occasionally useful for generating combinations (e.g., all date/user pairs) when paired with a WHERE clause that filters most results away.

Compatibility

Filter by Database
SQL CROSS JOIN Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.
PostgreSQL Native all CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.
SQL Server Native all CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.
Oracle Native all CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.
SQLite Native all CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.

Details

Rarely needed in application code but useful for generating combinations or test data. N rows × M rows = N*M result rows.

Standard Syntax

SELECT a.col, b.col FROM table_a a CROSS JOIN table_b b;

Version Support

MySQL: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.

SELECT a.col, b.col FROM a CROSS JOIN b;

PostgreSQL Native syntax

CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.

SELECT a.col, b.col FROM a CROSS JOIN b;

SQL Server Native syntax

CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.

SELECT a.col, b.col FROM a CROSS JOIN b;

Oracle Native syntax

CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.

SELECT a.col, b.col FROM a CROSS JOIN b;

SQLite Native syntax

CROSS JOIN keyword or comma syntax (FROM a, b) both produce a cartesian product.

SELECT a.col, b.col FROM a CROSS JOIN b;