What is the difference between Natural Join and Inner Join?

Question

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

This what W3Schools says about Inner Join. I was reading database management by Korth and one chapter in it was on relational algebra. In that, there was a Natural Join which, in my limited understanding, is the same as Inner Join.

Can someone please tell me if there is a difference between the two or they are different names of referring to the same thing.

Duplicate of http://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join/8696402?noredirect=1#comment33678249_8696402. — , Mar 05 '14 at 22:27

score 7 · Accepted Answer · answered Sep 11 '13 at 14:41

7

A Natural Join is a form of Inner Join where the join is implicitly across all columns of matching names on both sides of the join.

E.g.

Table A
abc int
def int
ghi varchar(20)

Table B
abc int
def int
jkl int

A natural join between tables A and B would be the same as an inner join on columns abc and def.

Inner joins that could not be replaced with a Natural Join:

TableA
   inner join
TableB
   on
       TableA.Column1 = TableB.Column2 --Column names don't match

or

TableA
   inner join
TableB
   on
       TableA.Column1 >= TableB.Column1 --Not equality

answered Sep 11 '13 at 14:41

Damien_The_Unbeliever

234,701
27
340
448

So what is other form of Inner Join ? – An SO User Sep 11 '13 at 14:42
@LittleChild - any join where the join conditions use different columns on both sides, or are not joining based on equality. – Damien_The_Unbeliever Sep 11 '13 at 14:43
Natural Join = Equi Join ? – An SO User Sep 11 '13 at 14:44
@LittleChild equijoin is any join that is based on equality condition. any natural join is equijoin but not any equijoin is natural join. – Kirill Leontev Sep 11 '13 at 14:51
@beherenow I quit :D It is getting far too convoluted for me to grasp :p – An SO User Sep 11 '13 at 14:54
There are also Natural Outer Joins. Not sure if they are in SQL standard or implemented by vendors as feature. – ypercubeᵀᴹ Sep 11 '13 at 14:54
1

Just draw it up in Venn diagrams and it'll make sense. – Andrew Clear Sep 11 '13 at 15:06

score 2 · Answer 2 · answered Sep 12 '15 at 13:35

Natural Join and Inner Join are not same commands.
Natural Join joins the tables on the basis of equality of values of common columns without typing the condition in query......while Inner join joins tables on the basis of condition specified in query which can be "=" or ">=" or "<="....

Natural Join :

tab1 NATURAL JOIN tab2;

this will automatically check for equality of values of common columns

Iner Join:

tab1 INNER JOIN tab2 ON (condition);

Moreover, NATURAL JOIN gives common column once in the output of query while INNER JOIN gives common columns of both tables

What is the difference between Natural Join and Inner Join?

2 Answers2