Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Joins in sql server, Inner Join,Cross Join,Left Outer Join,Equi join, Right Outer Join, Full Outer Join

Dec 10, 2011
Introduction:

In this post I will explain what are the Joins in SQL Server and different types of Joins example (SQL LEFT outer Join, SQL RIGHT outer Join, SQL FULL outer Join, SQL Cross Join, SQL inner Join sample, Self Join example) and uses of Joins in SQL Server.

Description:

In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table.

Before enter into Joins concept first design two tables in database and enter data like as shown below

Create one table with primary key and give name as “UserDetails”

UserID
UserName
FirstName
LastName
     1
SureshDasari
Suresh
Dasari
     2
PrasanthiDonthi
Prasanthi
Donthi
     3
MaheshDasari
Mahesh
Dasari
Here UserID is the Primary key in UserDetails table

After that create another table with Foreign Key and give name as OrderDetails

OrderID
OrderNo
UserID
     1
543224
1
     2
213424
2
     3
977776
3
     4
323233
3
     5
998756
1
Here OrderID is the Primary key and UserID is the foreign key in OrderDetails table.

SQL contains different types of Joins we will see each concept with example by using above tables.

Types of Joins

            1) Inner Joins
            2) Outer Joins
            3) Self Join

Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view Designer.

Syntax for Inner Join

SELECT t1.column_name,t2.column_name
FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name=t2.column_name
Now check the below query for inner join

Example

SELECT u.UserName,u.LastName,o.OrderNo
FROM UserDetails u
INNER JOIN OrderDetails o
ON u.UserID=o.UserID
Once we run that query our output will be like this

UserName
LastName
OrderNo
     SureshDasari
Dasari
543224
     PrasanthiDonthi  
Donthi
213424
     MaheshDasari
Dasari
977776
     MaheshDasari
Dasari
323233
     SureshDasari
Dasari
998756
We can write our inner join query like this also it will give same result

SELECT u.UserName,u.LastName,o.OrderNo
FROM UserDetails u
JOIN OrderDetails o
ON u.UserID=o.UserID
Based on above result we can say that INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "UserDetails" that do not have matches in "OrderDetails", those rows will NOT be listed.

In inner Join we are having different types of Joins those are

          1) Equi Join
2        2) Natural Join
           3) Cross Join

Equi Join

 The Equi join is used to display all the matched records from the joined tables and also display redundant values. In this join we need to use * sign to join the table.

Syntax for Equi Join

SELECT * FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name=t2.column_name
Now check the below query for Equi join

Example

SELECT *
FROM UserDetails u
INNER JOIN OrderDetails o
ON u.UserID=o.UserID
Once we run above query our output will be like this

UserID
UserName
FirstName
LastName
OrderID
OrderNo
UserID
   1
 SureshDasari
Suresh
Dasari
     1
543224
     1
   2
 PrasanthiDonthi
Prasanthi
Donthi
     2
213424
     2
   3
 MaheshDasari
Mahesh
Dasari
     3
977776
     3
   3
 MaheshDasari
Mahesh
Dasari
     4
323233
     3
   1
 SureshDasari
Suresh
Dasari
     5
998756
     1
In equi join we need to use only equality comparisons in the join relation. If we use other operators such as (<,>) for our comparison condition then our Joins disqualifies for equi join.

Natural Joins

The Natural join is same as our Equi join but only the difference is it will restrict to display redundant values.

Syntax for Natural Join

SELECT * FROM table_name1 t1
NATURAL JOIN table_name2 t2
Example

SELECT *
FROM UserDetails
NATURAL JOIN OrderDetails
Note: These NATURAL Joins won’t work in our SQL Server (only supports in Oracle) it will throw syntax error. If you observe above code "NATURAL" is not highlighted, indicating that it is not recognized as a keyword.

Cross Join

A cross join that produces Cartesian product of the tables that involved in the join. 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.

Syntax for Cross Join

SELECT * FROM table_name1
CROSS JOIN table_name2
 Or we can write it in another way also

SELECT * FROM table_name1,table_name2
Now check the below query for Cross join

Example

SELECT * FROM UserDetails
CROSS JOIN OrderDetails
Or

SELECT * FROM UserDetails, OrderDetails
Once we run that query our output will be like this

UserID
UserName
FirstName
LastName
OrderID
OrderNo
UserID
1
SureshDasari
Suresh
Dasari
1
543224
1
1
SureshDasari
Suresh
Dasari
2
213424
2
1
SureshDasari
Suresh
Dasari
3
977776
3
1
SureshDasari
Suresh
Dasari
4
323233
3
1
SureshDasari
Suresh
Dasari
5
998756
1
2
PrasanthiDonthi
Prasanthi
Donthi
1
543224
1
2
PrasanthiDonthi
Prasanthi
Donthi
2
213424
2
2
PrasanthiDonthi
Prasanthi
Donthi
3
977776
3
2
PrasanthiDonthi
Prasanthi
Donthi
4
323233
3
2
PrasanthiDonthi
Prasanthi
Donthi
5
998756
1
3
MaheshDasari
Mahesh
Dasari
1
543224
1
3
MaheshDasari
Mahesh
Dasari
2
213424
2
3
MaheshDasari
Mahesh
Dasari
3
977776
3
3
MaheshDasari
Mahesh
Dasari
4
323233
3
3
MaheshDasari
Mahesh
Dasari
5
998756
1

Outer Joins

A join that return all the rows that satisfy the condition and unmatched rows in the joined table is an Outer Join.

We are having three types of Outer Joins

            Left Outer Join
             Right Outer Join
             Full Outer Join

Left Outer Join

The left outer join displays all the rows from the first table and matched rows from the second table.

Syntax for Left Outer Join

SELECT Column_List FROM table_name1 t1
LEFT OUTER JOIN table_name2 t2
ON t1.column_name=t2.column_name
Now check the below query for Left Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo
FROM UserDetails u
LEFT OUTER JOIN OrderDetails o
ON u.UserID=o.UserID
Once we run that query our output will be like this

UserID
UserName
OrderNo
1
SureshDasari
543224
1
SureshDasari
998756
2
PrasanthiDonthi
213424
3
MaheshDasari
977776
3
MaheshDasari
323233

Right Outer Join

The right outer join displays all the rows from the second table and matched rows from the first table.

Syntax for Right Outer Join

SELECT Column_List FROM table_name1 t1
RIGHT OUTER JOIN table_name2 t2
ON t1.column_name=t2.column_name
Now check the below query for Right Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo
FROM UserDetails u
RIGHT OUTER JOIN OrderDetails o
ON u.UserID=o.UserID
Once we run that query our output will be like this

UserID
UserName
OrderNo
1
SureshDasari
543224
2
PrasanthiDonthi
213424
3
MaheshDasari
977776
3
MaheshDasari
323233
1
SureshDasari
998756

Full Outer Join

Full Outer Join displays all the matching and non matching rows of both the tables.

Syntax for Full Outer Join

SELECT Column_List FROM table_name1 t1
FULL OUTER JOIN table_name2 t2
ON t1.column_name=t2.column_name
Now check the below query for Full Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo
FROM UserDetails u
FULL OUTER JOIN OrderDetails o
ON u.UserID=o.UserID
Once we run that query our output will be like this

UserID
UserName
FirstName
LastName
OrderID
OrderNo
UserID
1
SureshDasari
Suresh
Dasari
1
543224
1
1
SureshDasari
Suresh
Dasari
5
998756
1
2
PrasanthiDonthi
Prasanthi
Donthi
2
213424
2
3
MaheshDasari
Mahesh
Dasari
3
977776
3
3
MaheshDasari
Mahesh
Dasari
4
323233
3

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 where clause.

To implement self join first design table and give a name as “EmployeeDetails

EmpID
EmpName
EmpMgrID
1
Suresh
2
2
Prasanthi
4
3
Mahesh
2
4
Sai
1
5
Nagaraju
1
6
Mahendra
3
7
Sanjay
3

Now I want to get manager names of particular employee for that we need to write query like this

select e2.EmpName,e1.EmpName as 'Manager'
from EmployeeDetails e1
INNER JOIN EmployeeDetails e2
on e1.EmpID=e2.EmpMgrID
Here if you observe above query EmployeeDetails table joined itself using table aliases e1 and e2.

After that run our query output will be like this

EmpName
Manger
Sai
Suresh
Nagaraju
Suresh
Suresh
Prasanthi
Mahesh
Prasanthi
Mahendra
Mahesh
Sanjay
Mahesh
Prasanthi
Sai
 

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

70 comments :

Anonymous said...

Dear Suresh,
I had three table
1. tEmployeeInfo(EmpID, EmpName, )
2. tEmployeeList(EmpID, RoleID)
3. tServiseCustMapping(RoleID)

In tServiseCustMapping, thr are three column
tblID, SERProviderRoleID, CUSRoleId
which display the relation that which RoleID is ServiceProvider of which Customer(Both Service Prov. and Cutomer is represent by Role ID)
and tEmployeeInfo shows the which employeeID is has which RoleID.
And last tEmployeeList has EmployeeName and all Employee INfo.
A RoleID can hav Multiple EmployeeID but One EmployeeID can have one RoleID.
If a ServProv RoleID has Two Employee and Customer RoleID has Three EmployeeID.
Then I Need To show in GridView All Service Provider and Customer Relation with based On Employee based.

Please Help Me.
Thanks
Shamim Ansari

Unknown said...

hey very nice articles!!!
can you post a article describing all the types of user defined function,scaler function of SQL

zaidzaidi said...

GREAT EXPLANATION.........

I clear join concept from here .....

Anonymous said...

good

Jack said...

very good

jyo said...

hi sir,
i have id,name in one table,if i select name i shoud get name of the id.give me a solution for tis

hello friends said...

UUO

Anonymous said...

good

Anonymous said...

how to calculate cummulative target,sales and runrate based on cummulative target and sales
on single table with financial year from 2012-13 to 2014-14(example) and weeks colum 1-52 for pericular year

Anonymous said...

k

Anonymous said...

nice

Anonymous said...

Hi Suresh,

It seems good with explanation.
Great

Thanks,

manish arora said...

good

Anonymous said...

good

Anish yandrapragada said...

Hi suresh, its nice to read ur article but it will be very useful and understandable if u provide the different outputs for different joins .

I am not getting the actual idea of what u have given the above example of inner joins and outer joins

so, can u plz keep the modified article?

Anonymous said...

yaa mate awesome perfect explanation like people understanding their own handwriting in there books than sumone else ... loud and clear

Anonymous said...

Very clear explanation...helps a lot

Anonymous said...

gud

bandita bahubalendra said...

Very nice article with clear explanation ...Thank you!!!

Anonymous said...

thnks.......

Anonymous said...

gud,but some more examples pls

Anonymous said...

niceeeeeee..........

Anonymous said...

nice

Anonymous said...

Really a good explanation of joins very clearer

Anonymous said...

very good

Praveen said...

good article

Anonymous said...

it really helpd me lot :) thanks

Anonymous said...

its very use full ....

Manish said...

Dear Suresh,
I am one of regular readers of your blog. Today when I was reading your article about the Joins (http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.html) I noticed that in the explanation of Full Outer Join you have given correct syntax but in the example there is mistake .
Your syntax is:
SELECT Column_List FROM table_name1 t1
FULL OUTER JOIN table_name2 t2
ON t1.column_name=t2.column_name

and Example is:
SELECT u.UserID,u.UserName,o.OrderNo
FROM UserDetails u
RIGHT OUTER JOIN OrderDetails o
ON u.UserID=o.UserID

so instead of using Full Outer Join you have specified Right Outer Join.
Please correct the above mentioned mistake.

Thanks & Regards:
Manish Kumar










Suresh Dasari said...

@Manish...
Thanks. i updated the post....

Mar Fahim said...

Hi Suresh,

It seems good with explanation.
Great

Thanks,

Anonymous said...

Thank you...

Unknown said...

Very Gud article...

Jaya Kumar said...

Dear Sir,

I have lot of Doubts in Number series formats with auto increment,please help me the above.

Thanks and Regards,
Jayakumar

Dinesh Babu said...

verry good

Omkant Rajput said...

HHH

Anonymous said...

Great...
Thanks.

Abhinavsingh993 said...

HOW TO CALL IT FROM .CS PAGE OR .ASPX PAGE IS A QUESTION FOR THE FRESHERS LIKE ME,
OTHERWISE THIS ARTICLE IS AS GREAT LIKE YOU SIR,
HAT'S OFF TO YOU

Anonymous said...

nice

Anonymous said...

Clear and helpful
Thanks

Anonymous said...

Hey..
its very clear and easy to understand
helped a lot...Thanks!

Babusoft said...

good one clear topics

pavan kumar said...

good

Anonymous said...

way of explanation good

Anonymous said...

you clear the join concept vry clrly....
and i love ur website which explain the concepts about .net and other technologies.

prajakta pawar said...

very nice explanation it is easy to understand...

yogeesha sp said...

wat an exalent explanation suresh sir,,with in one hour i forfectlly learned all join querys,,,thanks thanku so much,,,love u ganeshh,,,,really wen i studing ur every concept i fell like noting is der in dot net,,thanku suresh sirrrrrr,,,,,

Shikha said...

Hi..I am Assit.Prof.Shikha Shah, from PIT college,Gujarat Technological University,Ahmadabad.I have started my research work on JOIN Algorithm basically based on DINER(DOUBLE INDEX NESTED REACTIVE JOIN) Approach.So i wish to improve efficiency of this approach and also make this approach more efficient and reduce time complexity by making less comparisons between outer and inner table of existing algorithm by modify or rewrite existing approach. So please provide me guidance that how can i reduce time complexity by making less comparisons ...
Hoping For Your Valuable Feedback..
Thanking You..
Assit.Prof.Shikha Shah

Anonymous said...

Nice explanation with simple language.................tanxsss.. :)

Rajshekhar Reddy said...

nice explanation

Munesh Saharia said...

can u contact me munesh.saharia@gmail.com

Dilip Singh said...

nice explanation.

Anonymous said...

good explanation

Anonymous said...

Jee Bawro Ho Gayo :)

Anonymous said...

Thanks a lot......
Good Explanation

Patel Hitesh said...

Very nice yar...........

Riyas Mohamed said...

Hi sir,

I have a table which is called menutable.

it has a following field.

SNO(Auto Increament), Menuname(varchar((20)), Submenu(int) , statuz(int)

Sno is a primary key.

my question is how to set the sno is a foreign key for same table (submenu)

please reply me............

Thanks advanced.....

Juber N Mulani said...

Good one !!

Raj k said...

good explantaion & easy understanding.

Saprativa Chattaraj said...

good explanation.....................@@@@@@@

Anonymous said...

Hi suresh,
Very good articles.

chandu said...

very good artical....

avneet kaur said...

it is very useful in my assignment
thanks

Litan Sarker said...

Simply,awesome....,

Arjun Parekh said...

please can you give me INSERT, UPDATE and DELETE queries for the FULL OUTER join of above tables... I will be very much greatful to you

Ashutosh Kumar said...

Very nice and easy explanation , thanks

Uttam K said...

SImple and best article, indeed...

Rakesh Kumar Jayswal said...

thanks ....:)

Anonymous said...

Hi suresh,everything is fine and well explained but the result of full outer join is not fit as per the query.

Anonymous said...

Employees Table
EmpName EmpID EmpAge
Raju 01 21
Rani 02 29
Anu 03 43

AgeGroups Table
AgeGroupID AgeGroupName Min Age Max Age
1 Young Age 21 29
2 Middle Age 30 39
3 Old Age 40 60

Q) write a query to display AgeGroupName based on Employee Age ?

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.