28/06/2015
TypesofJoininSQLServerCodeProject
11,561,474 members 48,592 online
articles
Q&A
Sign in
forums
lounge
Searchforarticles,questions,tips
Types of Join in SQL Server
Nirav Prabtani, 20 Jan 2014
CPOL
Rate this:
4.81 23 votes
Types of join in SQL Server for fetching records from multiple tables.
Introduction
In this tip, I am going to explain about types of join.
What is join??
An SQL JOINclause is used to combine rows from two or more tables, based on a common field between
them.
There are many types of join.
Inner Join
1. Equijoin
2. Natural Join
Outer Join
1. Left outer Join
2. Right outer join
3. Full outer join
Cross Join
Self Join
Using the Code
Joinis very useful to fetching records from multiple tables with reference to common column between
them.
[Link]
1/10
28/06/2015
TypesofJoininSQLServerCodeProject
To understand joinwith example, we have to create two tables in SQL Server database.
1. Employee
Hide Copy Code
createtableEmployee(
idintidentity(1,1)primarykey,
Usernamevarchar(50),
FirstNamevarchar(50),
LastNamevarchar(50),
DepartIDint
2. Departments
Hide Copy Code
createtableDepartments(
idintidentity(1,1)primarykey,
DepartmentNamevarchar(50)
Now fill Employeetable with demo records like that.
Fill Departmenttable also like this....
1 Inner Join
The join that displays only the rows that have a match in both the joined tables is known as inner join.
Hide Copy Code
[Link],[Link],[Link],e2.DepartmentName_
[Link]=[Link]
[Link]
2/10
28/06/2015
TypesofJoininSQLServerCodeProject
It gives matched rows from both tables with reference to DepartIDof first table and id of second table like
this.
EquiJoin
Equi join is a special type of joinin which we use only equality operator. Hence, when you make a query for
joinusing equality operator, then that joinquery comes under Equi join.
Equi join has only = operator in joincondition.
Equi join can be inner join, left outer join, right outer join.
Check the query for equijoin:
Hide Copy Code
SELECT*[Link]=[Link]
2 Outer Join
Outer join returns all the rows of both tables whether it has matched or not.
We have three types of outer join:
1. Left outer join
2. Right outer join
3. Full outer join
a Left Outer join
Left join displays all the rows from first table and matched rows from second table like that..
Hide Copy Code
SELECT*FROMEmployeee1LEFTOUTERJOINDepartmentse2
[Link]
3/10
28/06/2015
TypesofJoininSQLServerCodeProject
[Link]=[Link]
Result:
b Right outer join
Right outer join displays all the rows of second table and matched rows from first table like that.
Hide Copy Code
SELECT*FROMEmployeee1RIGHTOUTERJOINDepartmentse2
[Link]=[Link]
Result:
3 Full outer join
Full outer join returns all the rows from both tables whether it has been matched or not.
Hide Copy Code
SELECT*FROMEmployeee1FULLOUTERJOINDepartmentse2
[Link]=[Link]
Result:
[Link]
4/10
28/06/2015
TypesofJoininSQLServerCodeProject
3 Cross Join
A cross join that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian
product is the number of the rows in the first table multiplied by the number of rows in the second table like
this.
Hide Copy Code
SELECT*FROMEmployeecrossjoinDepartmentse2
You can write a query like this also:
Hide Copy Code
SELECT*FROMEmployee,Departmentse2
4 Self Join
Joining the table itself called self join. Self join is used to retrieve the records having some relation or
similarity with other records in the same table. Here, we need to use aliases for the same table to set a self
join between single table and retrieve records satisfying the condition in whereclause.
Hide Copy Code
[Link],[Link],e1.LastNamefromEmployeee1_
[Link]=[Link]
Here, I have retrieved data in which idand DepartIDof employeetable has been matched:
[Link]
5/10
28/06/2015
TypesofJoininSQLServerCodeProject
Points of Interest
Here, I have taken one example of self join in this scenario where manager name can be retrieved by
manageridwith reference of employee id from one table.
Here, I have created one table employeeslike that:
If I have to retrieve manager name from manager id, then it can be possible by Self join:
Hide Copy Code
[Link],e2.empNameasEmpName_
[Link]=[Link]
Result:
History
20 Jan 2014: Initial post
License
This article, along with any associated source code and files, is licensed under The Code Project Open License
CPOL
Share
EMAIL
[Link]
TWITTER
6/10
28/06/2015
TypesofJoininSQLServerCodeProject
About the Author
Nirav Prabtani
Web Developer Satva Infotech
India
Nirav Prabtani
I am a software engineer at Satva Infotech, Database Architect and Designer /Technical
Architect/Analyst
Programmer in Microsoft .NET Technologies & Microsoft SQL Server with more than
2 years of hands on experience.
I love to code....!!!
My recent past includes my work with the education domain as a technical business
requirement analyst, database architect & designer and analyst programmer; just
love my involvement with the world of knowledge, learning and education and I think
I know quite well what I want to do in life & in my career. What do I like? Well,
ideation, brainstorming, coming up with newer and more creative ways of doing things;
each time with an enhanced efficiency. An item in my day's agenda always has a task
to look at what I did yesterday & focus on how I can do it better today
Contact Me
[Link]
7/10
28/06/2015
TypesofJoininSQLServerCodeProject
Nirav Prabtani
Mobile : +91 738 308 2188
Email : niravjprabtani@[Link]
My Blog:
Nirav Prabtani
You may also be interested in...
SQL Joins
Value of Database Resilience:
Comparing Costs of Downtime
for IBM DB2 10.5 and Microsoft
SQL Server 2014
Visual Representation of SQL
Joins
Understanding the Need for a
Single Code Base in Enterprise
Mobile App Development
SQL Joins
6 Productivity Hacks for the
Enterprise Developer
Comments and Discussions
You must Sign In to use this message board.
Search Comments
Go
First Prev Next
Adition things About Natual join.
ashu_om 10Nov14 18:02
[Link]
8/10
28/06/2015
TypesofJoininSQLServerCodeProject
Thanks Nirav for simple description , i wann add about natual join few bit on the same aritical.
Natural Join :
SELECT * FROM Emp NATURAL JOIN Dept it implicitly compar by Dept_id because the common
column in the table
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.
Ashutosh Soni
Software Engineer
MumbaiIndia
Sign In ViewThread Permalink
My vote of 5
Sibeesh KV 19Sep14 19:43
5
Sign In ViewThread Permalink
5.00/5 1 vote
My vote of 5
Animesh Datta 21May14 22:13
Nice Tip ... but where is the exampleoutput of Cross Join .
Sign In ViewThread Permalink
5.00/5 1 vote
Re: My vote of 5
Nirav Prabtani 26May14 20:49
thank you for +5 and i don't think there is need of example for cross join but you can see the
example at..
Cross join
Sign In ViewThread Permalink
Join in sql server
Member 9954224 24Apr14 5:53
Join is used to combine two or more table in a single table on the basis of condition or without
condition.
Suppose we have two tables
[Link]
9/10
28/06/2015
TypesofJoininSQLServerCodeProject
1. Employee We have two field in this table Emp_Id,Emp_Name
2. Emp_Department We have three filed in this table Id,Emp_id,Emp_dept
If we want to fetch Emp_Dept with Emp_name than we have to use Join keyword in our query.
I am ging to write some Example I hope you will Like this.
[Link]
Sign In ViewThread Permalink
1.00/5 7 votes
Some points
Christian Graus 20Jan14 14:17
A self join is an inner join, it's not really a join type. You should also mention that self joins can be
quite expensive.
"SELECT * FROM Employee e1 JOIN Departments e2 ON [Link] = [Link] " is just an inner join. An
equi join is not standard SQL and looks like this:
SELECT * FROM Employee e1,Departments e2 WHERE [Link] = [Link]
It's VERY expensive, as the , operator does a cross join and then the WHERE clause filters it down.
Christian Graus
My new article series is all about SQL !!!
Sign In ViewThread Permalink
Re: Some points
Nirav Prabtani 21Jan14 2:34
Thank you very much for this points
Sign In ViewThread Permalink
Refresh
General
News
1
Suggestion
Question
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150624.2 | Last Updated 20 Jan 2014
Bug
Answer
Select Language
Layout: fixed | fluid
[Link]
Joke
Rant
Admin
Article Copyright 2014 by Nirav Prabtani
Everything else Copyright CodeProject, 19992015
10/10
