w3resource logo


SQL natural join

SQL Natural Join

rating Average rating 7 out of 10. Total 163 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

Name Description
table1, table2 Name of the tables participating in joining.

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

Sql natural join

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 details

SELECT * 
FROM foods;

Output

foods details

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

inner-join-on-company-and-food.png

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

SELECT * 
FROM company
NATURAL JOIN foods;

Output

natural join on company and foods tables

The repeated column is eliminated, but in both cases 4 rows returned

Key points to remember

Click on the following to get the slides presentation -

SQL JOINS, slide presentation

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.



<<PreviousNext>>