This article introduces the concept of SQL natural join, a type of join that automatically matches columns with the same name and type. It also shows you how to use the natural join syntax and some examples to practice
A natural join is a type of equijoin, which means it works by matching equal values in common columns. Unlike other joins, we do not need to explicitly specify the column for joining, as SQL automatically takes care of finding the common column names and data types and joins the data.
The key features of a natural join are:
The syntax for a natural join in SQL is fairly simple and straightforward. It brings together two or more tables by just using the NATURAL JOIN keywords without needing to specify the actual join columns with the ON or USING clause. The basic syntax is:
SELECT column1, column2, column3,...columnN
FROM tablename1
NATURAL JOIN tablename2
Here, columns 1 to N are the names of the columns that you want to retrieve data from in the result set after the join operation. The first table name specifies the left table, and the second tablename is the right table for the join. The NATURAL JOIN keywords inform the SQL engine to automatically join these tables on columns with the same name and data type.
Performing a natural join in SQL involves just three simple steps:
Following these 3 steps and using the built-in NATURAL JOIN feature allows for easily joining SQL tables without explicitly stating the join columns.
Consider the following student and fee tables with common column names and data types; here’s a natural join SQL example:
Student
Roll No. Name Contact
1 John 1234567890
2 Sam 0987654321
Fee
Roll No. Amount Paid
1 5000 Yes
2 4000 No
The SQL query for a natural join:
SELECT s.Name, s.Contact, f.Amount, f.Paid
FROM Student s
NATURAL JOIN Fee f;
Output:
Name Contact Amount Paid
John 1234567890 5000 Yes
Sam 0987654321 4000 No
The tables were joined based on the common RollNo column.
There are some key differences between SQL natural joins and inner joins:
So, in essence, natural joins are easier to implement, while inner joins are more flexible and customizable during the table joining process in SQL.