w3resource logo


SQL natural join

SQL Natural Join

<<PreviousNext>>

Seondary Nav

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 same name of associate tables will appear once only.

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 :

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

Pictorial presentation of the above Natural Join :

sql natural join example

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 :

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
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 :

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
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

Key points to remember

Click on the following to get the slides presentation -

SQL JOINS, slide presentation



<<PreviousNext>>

 

Looking for some other tutorial?