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:
-- This query selects all columns from the resulting joined table of two tables using a natural join.
SELECT * -- Selecting all columns from the resulting joined table.
FROM foods -- Specifying the first table to select data from, which is 'foods'.
NATURAL JOIN company; -- Performing a natural join with the 'company' table.
Explanation:
- The SQL code retrieves data from two tables, 'foods' and 'company', and combines them into a single result set using a natural join.
- The query selects all columns from the resulting joined table, which includes columns from both 'foods' and 'company'.
- It uses the 'NATURAL JOIN' keyword combination to specify the type of join, which is a natural join. This type of join automatically joins the two tables based on columns with the same name and data type.
- The natural join condition implicitly matches columns with the same name and data type in both tables, resulting in a join on those columns.
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 * -- Selecting all columns from the resulting joined table.
FROM company -- Specifying the first table to select data from, which is 'company'.
INNER JOIN foods -- Specifying the type of join, which is an inner join, and specifying the second table to join, which is 'foods'.
ON company.company_id = foods.company_id;
-- Specifying the condition for joining the two tables, which is where the 'company_id' column in the 'company' table matches the 'company_id' column in the 'foods' table.
Explanation:
- The SQL code retrieves data from two tables, 'company' and 'foods', and combines them into a single result set based on a specific condition, using an inner join.
- The query selects all columns from the resulting joined table, which includes columns from both 'company' and 'foods'.
- It uses the 'INNER JOIN' keyword combination to specify the type of join, which is an inner join. This means only the rows that have matching values in both tables are included in the result set.
- The 'ON' clause specifies the condition for joining the two tables, where the 'company_id' column in the 'company' table matches the 'company_id' column in the 'foods' table.
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 * -- Selecting all columns from the resulting joined table.
FROM company -- Specifying the first table to select data from, which is 'company'.
NATURAL JOIN foods; -- Performing a natural join with the 'foods' table.
Explanation:
- The SQL code retrieves data from two tables, 'company' and 'foods', and combines them into a single result set using a natural join.
- The query selects all columns from the resulting joined table, which includes columns from both 'company' and 'foods'.
- It uses the 'NATURAL JOIN' keyword combination to specify the type of join, which is a natural join. This type of join automatically joins the two tables based on columns with the same name and data type.
- The natural join condition implicitly matches columns with the same name and data type in both tables, resulting in a join on those columns.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics