Skip to main content

Avoid Using CROSS JOIN

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.

PawSQL Auditing​

PawSQL provides a corresponding SQL audit warning for this case.

Default Warning Level​

  • Warning

There are 3 warning levels in ascending order: Note < Warning < Error

Trigger Conditions​

  • SQL statement contains CROSS JOIN syntax

Database Types and Versions​

  • MySQL
  • openGauss
  • Oracle
  • PostgreSQL
  • KingbaseES
  • MariaDB

About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The database types supported include MySQL, PostgreSQL, Oracle, and openGauss; and the products provided by PawSQL include:

  • PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
  • PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.
  • PawSQL Engine, which is the backend optimization engine of the PawSQL series of products, can be installed and deployed independently, and provides SQL optimization services through http/json interfaces. PawSQL Engine is provided for deployment and installation as a docker image.

Contact Us​

Website: https://docs.pawsql.com

Email: service@pawsql.com

Twitter: https://twitter.com/pawsql