Avoid Using CROSS JOIN
· 2 min read
Optimize your SQL Queries by Clicks!
Copyright © 2023 PawSQL
Problem Definition
CROSS JOIN performs a Cartesian product between every row of the first table and every row of the second table. It generates table1 rows x table2 rows records. Theoretically it is equivalent to an inner join with condition 1=1. CROSS JOIN can quickly join multiple tables but also generates a large number of records causing low efficiency. Also with no join condition specified, the result may not have practical meaning.
PawSQL provides a risk warning for statements using CROSS JOIN to avoid potential performance issues caused by it.
Example SQL:
SELECT *
FROM lineitem
CROSS JOIN orders;
This combines every record in the lineitem table with all records in the orders table.