Difference between Equi Join, Natural Join and Cross Join in sql - Java @ Desk

Tuesday, August 13, 2013

Difference between Equi Join, Natural Join and Cross Join in sql

Different types of Inner Join are :
1) Equi Join
2) Natural Join
3) Cross Join

One of the most common interview questions that is being asked is explain Equi Join, Natural Join and Cross Join in sql. I will break the question in different meaningful easy to understand concepts.

Consider the below tables
Employee
EmpId EmpName
1 John
2 David
3 Peter
4 Eric
Area
AreaId AreaName EmpId
3 New York 1
4 Canada 3
5 Australia 3
6 England 4


Equi join
Equi join is the first type of Inner Join.
It joins two or more tables where the specified columns are equal.
In this type of join, you can only use '=' operator in comparing the columns.
Operators like '>', '<' are not allowed in this type of join.
Example query: Select * from Employee emp
INNER JOIN Area area on area.EmpId = emp.EmpId
Result:
EmpId EmpName AreaId AreaName EmpId
3 Peter 4 Canada 3
3 Peter 5 Australia 3
4 Eric 6 England 4
As shown above, EmpId column is appearing twice.



Natural join
It is same as equi­join but the difference is that in natural join, the common attribute appears only once.
Example query: Select * from Employee emp
NATURAL JOIN Area area on area.EmpId = emp.EmpId
Result:

EmpId EmpName AreaId AreaName
3 Peter 4 Canada
3 Peter 5 Australia
4 Eric 6 England
As shown above, EmpId column is appearing only once.



Cross join
A cross join that produces Cartesian product of the tables.. The size of a Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.
Example query: Select * from Employee emp
CROSS JOIN Area area
Result:
EmpId EmpName AreaId AreaName EmpId
1 John 3 New York 1
1 John 4 Canada 3
1 John 5 Australia 3
1 John 6 England 4
2 David 3 New York 1
2 David 4 Canada 3
2 David 5 Australia 3
2 David 6 England 4
3 Peter 3 New York 1
3 Peter 4 Canada 3
3 Peter 5 Australia 3
3 Peter 6 England 4
4 Eric 3 New York 1
4 Eric 4 Canada 3
4 Eric 5 Australia 3
4 Eric 6 England 4

As shown in the results above, the number of rows returned in the result are 4 * 4 = 16.






5 comments:

  1. is the second row in the cross join example correct? I was expecting 4x jhons, 4x david, ect ect

    ReplyDelete
  2. Nice observation. Corrected the results. Please verify

    ReplyDelete
  3. in the result of natural join and equi join one more column should be added
    where emp_id=1.
    1 john 3 newyork

    ReplyDelete
  4. in result of natural join and equi join one more row should be added as
    1 john 3 newyork

    ReplyDelete
  5. we can not use where clause in natural join .

    ReplyDelete