Skip to main content

Avoid Using Natural Join

Copyright © 2023 PawSQL

Definition​

Natural join is a special type of equijoin that can be used with inner, outer and full joins. It automatically searches for all columns in both tables with the same name and type and performs an equijoin on those columns. Natural join can simplify the statement but implicit join conditions reduce code readability and understanding of relationships between tables, and are also prone to mistaken joins. PawSQL provides a risk warning for statements using natural join to avoid potential correctness issues caused by it.

Taking the lineitem and orders tables in the tpch database as an example:

SELECT *
FROM lineitem
NATURAL JOIN orders;

This will automatically join columns with the same name and type (like orderkey) between the lineitem and orders tables.

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

Conditions​

  • SQL statement contains NATURAL 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