SQL Natural Join
What is Natural Join in SQL?
We have already learned that an EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables and an equal sign (=) is used as comparison operator in the where clause to refer equality.
The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only.
Pictorial presentation of the above SQL Natural Join:

Natural Join: Guidelines
- The associated tables have one or more pairs of identically named columns.
- The columns must be the same data type.
- Don’t use ON clause in a natural join.
Syntax:
SELECT * FROM table1 NATURAL JOIN table2;
Example:
Here is an example of SQL natural join between tow tables:
Sample table: foods
Sample table: company
To get all the unique columns from foods and company tables, the following SQL statement can be used:
SQL Code:
SELECT *
FROM foods
NATURAL JOIN company;
Output:
COMPANY_ID ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_NAME COMPANY_CITY ---------- ---------- ------------------------- ---------- ------------------------- -------------- 16 1 Chex Mix Pcs Akas Foods Delhi 15 6 Cheez-It Pcs Jack Hill Ltd London 15 2 BN Biscuit Pcs Jack Hill Ltd London 17 3 Mighty Munch Pcs Foodies. London 15 4 Pot Rice Pcs Jack Hill Ltd London 18 5 Jaffa Cakes Pcs Order All Boston
Difference between natural join and inner join
There is one significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned. See the following example on company table and foods table :
SQL Code:
SELECT *
FROM company;
Output:
COMPANY_ID COMPANY_NAME COMPANY_CITY ---------- ------------------------- --------------- 18 Order All Boston 15 Jack Hill Ltd London 16 Akas Foods Delhi 17 Foodies. London 19 sip-n-Bite. New York
SQL Code:
SELECT *
FROM foods;
Output:
ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID ---------- ------------------------- ---------- ---------- 1 Chex Mix Pcs 16 6 Cheez-It Pcs 15 2 BN Biscuit Pcs 15 3 Mighty Munch Pcs 17 4 Pot Rice Pcs 15 5 Jaffa Cakes Pcs 18 7 Salt n Shake Pcs
The INNER JOIN of company and foods on company_id will return :
SQL Code:
SELECT *
FROM company
INNER JOIN foods
ON company.company_id = foods.company_id;
Output:
COMPANY_ID COMPANY_NAME COMPANY_CITY ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID ---------- --------------- --------------- ---------- --------------- ---------- ---------- 16 Akas Foods Delhi 1 Chex Mix Pcs 16 15 Jack Hill Ltd London 6 Cheez-It Pcs 15 15 Jack Hill Ltd London 2 BN Biscuit Pcs 15 17 Foodies. London 3 Mighty Munch Pcs 17 15 Jack Hill Ltd London 4 Pot Rice Pcs 15 18 Order All Boston 5 Jaffa Cakes Pcs 18
SQL Code:
SELECT *
FROM company
NATURAL JOIN foods;
Output:
COMPANY_ID COMPANY_NAME COMPANY_CITY ITEM_ID ITEM_NAME ITEM_UNIT ---------- --------------- --------------- ---------- --------------- ---------- 16 Akas Foods Delhi 1 Chex Mix Pcs 15 Jack Hill Ltd London 6 Cheez-It Pcs 15 Jack Hill Ltd London 2 BN Biscuit Pcs 17 Foodies. London 3 Mighty Munch Pcs 15 Jack Hill Ltd London 4 Pot Rice Pcs 18 Order All Boston 5 Jaffa Cakes Pcs
NATURAL JOINS: Relational Databases
Key points to remember
Click on the following to get the slides presentation -

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SQL INNER JOIN
Next: SQL CROSS JOIN
SQL: Tips of the Day
How to avoid the "divide by zero" error in SQL?
Select Case when divisor=0 then null Else dividend / divisor End ,,,
OR:
Select dividend / NULLIF(divisor, 0) ...
Ref: https://bit.ly/3dLj7gS
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook