What is the difference between natural join and equijoin in database?

Hitesh Dsouza
Nov 6 · 2 min read

While most developers are familiar with the various types of joins, I want to talk about natural joins and equijoin today.

For those who are still confused about the various types of joins, here’s an excellent diagram depicting the types of joins and the join query:

Now coming to Natural join vs. Equijoin, to simplify,

Equi Join is a join using one common column (referred to in the “on” clause). This join is a equally comparison join, thus not allowing other comparison operator such as <, > <= etc. The joining id needs to be exact match and doesn’t allow for any variations or filters like “having” clause. Here the join is explicitly mentioned.

Example:

SELECT * FROM table1 JOIN table2 ON (table1.id = table2.id)

Natural Join is an implicit join clause based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. This is a much riskier join as the join here is implied. The query attempts to use all columns with common names to create a join. An asterisk qualified by a table name (for example, Table_1.*) will be expanded to every column of that table that is not a common column.

Example:

SELECT * FROM table1 NATURAL JOIN table2

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade