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.
Select * FROM table1 NATURAL JOIN table2;
|table1, table2||Name of the tables participating in joining.|
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;
Pictorial presentation of the above Natural Join :
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;
SELECT * FROM foods;
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;
The NATURAL JOIN of company and foods on company_id will return :
SELECT * FROM company NATURAL JOIN foods;
The repeated column is eliminated, but in both cases 4 rows returned
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.