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

• Oracle NATURAL JOIN
• MySQL NATURAL JOIN
• SQLite NATURAL JOIN

Key points to remember

Click on the following to get the slides presentation -

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SQL INNER JOIN
Next: SQL CROSS JOIN