SQL Natural Join

has average rating 8 out of 10. Total 60 users rated.

<<PreviousNext>>

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;

Parameters

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

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;

Output

SELECT * FROM foods;

Output

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

The NATURAL JOIN of company and foods on company_id will return :

SELECT * FROM company
NATURAL JOIN foods;

Output

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.