Please enable Javascript to correctly display the contents on Dot Net Tricks!

Difference between inner join and equi join and natural join

  Author : Shailendra Chauhan
Posted On : 23 Feb 2013
Total Views : 189,595   
Updated On : 26 Sep 2016
 

SQL join clause is used to to retrieve data from two or more database tables. In previous article, I have explained the Different Types of SQL Joins. In this article, I would explain the difference among inner join, equi join and natural join.

Inner Join

This is the most used join in the SQL. this join returns only those records/rows that match/exists in both the database tables.

Inner Join Example

SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;
Inner Join Result
tblEmp.Name
tblEmp.DeptID
tblDept.Name
tblDept.DeptID
Ram
1
HR
1
Raju
2
IT
2
Soya
2
IT
2
Sam
3
ADMIN
3

In the join condition, you can also use other operators like <,>,<>.

Equi Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

Equi Join Example

SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;
--Using Clause is not supported by SQL Server
--Oracle and MySQL Query
SELECT * FROM tblEmp INNER JOIN tblDept USING(DeptID)
Equi Join Result
tblEmp.Name
tblEmp.DeptID
tblDept.Name
tblDept.DeptID
Ram
1
HR
1
Raju
2
IT
2
Soya
2
IT
2
Sam
3
ADMIN
3

Note

  1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.

  2. Equi join only have equality (=) operator in the join condition.

  3. Equi join can be an Inner join, Left Outer join, Right Outer join

  4. The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL.

Natural Join

Natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result have only one column for each pair of equally named columns.

Natural Join Example

--Run in Oracle and MySQL 
SELECT * FROM tblEmp NATURAL JOIN tblDept
Natural Join Result
DeptID
tblEmp.Name
tblDept.Name
1
Ram
HR
2
Raju
IT
2
Soya
IT
3
Sam
ADMIN

In the above join result we have only one column "DeptID" for each pair of equally named columns.

Note

  1. In Natural join, you can't see what columns from both the tables will be used in the join. In Natural join, you might not get the desired result what you are expecting.

  2. Natural join clause is not supported by SQL Server, it is supported by Oracle and MySQL.

References: http://support.microsoft.com/ and http://en.wikipedia.org/
What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.



Free Interview Books
 
COMMENTS (0)
11 MAR
ASP.NET MVC with Angular2 Development (Online)

09:00 PM - 11:00 PM IST( TUS, THR, SAT)

Know More
6 MAR
Master Class Fast Track MVC 5 with Angular2 Development (Online)

10:30 AM -03:30 PM IST (Fast Track)

Know More
6 MAR
Angular 2 Master Class Fast Track Online Development Training (Online)

10:30 AM- 01:30 PM IST

Know More
27 FEB
ASP.NET MVC with Angular2 Development (Online)

07:00 AM - 09:00 AM IST( MON, WED, FRI)

Know More
25 FEB
ASP.NET MVC with Angular2 Development (Classroom)

05:00 PM-06:30 PM

Know More
25 FEB
Xamarin Forms : Build Cross-platform Apps (Classroom)

3:00 PM-4:30 PM IST

Know More
25 FEB
ASP.NET MVC with Angular2 Development (Classroom)

3:00 PM-4:30 PM

Know More
14 FEB
.NET Development (Classroom)

11:00 AM-12:00 PM

31 JAN
ASP.NET MVC with Angular2 Development (Online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

30 JAN
NODEJS & MEAN Stack 2.x Development (Online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

15 JAN
PPC Marketing (Classroom)

04:00 PM-05:30 PM

10 JAN
ASP.NET MVC with AngularJS Development (Online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

31 DEC
ASP.NET MVC with AngularJS Development (Classroom)

09:30 AM-11:00 AM

5 NOV
ASP.NET MVC with AngularJS Development (Classroom)

08:00 AM-09:30 AM

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 

Professional Speaks

+