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
|
GohilRajni
|
Rajni
|
Gohil
|
2
|
GohilShivam
|
Shivam
|
Gohil
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
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
|
GohilRajni
|
Gohil
|
543224
|
GohilShivam
|
Gohil
|
213424
|
MaheshGohil
|
Gohil
|
977776
|
MaheshGohil
|
Gohil
|
323233
|
GohilRajni
|
Gohil
|
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
|
GohilRajni
|
Rajni
|
Gohil
|
1
|
543224
|
1
|
2
|
GohilShivam
|
Shivam
|
Gohil
|
2
|
213424
|
2
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
3
|
977776
|
3
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
4
|
323233
|
3
|
1
|
GohilRajni
|
Rajni
|
Gohil
|
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
|
GohilRajni
|
Rajni
|
Gohil
|
1
|
543224
|
1
|
1
|
GohilRajni
|
Rajni
|
Gohil
|
2
|
213424
|
2
|
1
|
GohilRajni
|
Rajni
|
Gohil
|
3
|
977776
|
3
|
1
|
GohilRajni
|
Rajni
|
Gohil
|
4
|
323233
|
3
|
1
|
GohilRajni
|
Rajni
|
Gohil
|
5
|
998756
|
1
|
2
|
ShivamGohil
|
Shivam
|
Gohil
|
1
|
543224
|
1
|
2
|
ShivamGohil
|
Shivam
|
Gohil
|
2
|
213424
|
2
|
2
|
ShivamGohil
|
Shivam
|
Gohil
|
3
|
977776
|
3
|
2
|
ShivamGohil
|
Shivam
|
Gohil
|
4
|
323233
|
3
|
2
|
ShivamGohil
|
Shivam
|
Gohil
|
5
|
998756
|
1
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
1
|
543224
|
1
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
2
|
213424
|
2
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
3
|
977776
|
3
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
4
|
323233
|
3
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
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
|
GohilRajni
|
543224
|
1
|
GohilRajni
|
998756
|
2
|
ShivamGohil
|
213424
|
3
|
MaheshGohil
|
977776
|
3
|
MaheshGohil
|
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
|
GohilRajni
|
543224
|
2
|
ShivamGohil
|
213424
|
3
|
MaheshGohil
|
977776
|
3
|
MaheshGohil
|
323233
|
1
|
GohilRajni
|
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
|
GohilRajni
|
Rajni
|
Gohil
|
1
|
543224
|
1
|
1
|
GohilRajni
|
Rajni
|
Gohil
|
5
|
998756
|
1
|
2
|
ShivamGohil
|
Shivam
|
Gohil
|
2
|
213424
|
2
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
3
|
977776
|
3
|
3
|
MaheshGohil
|
Mahesh
|
Gohil
|
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
|
Rajni
|
2
|
2
|
Shivam
|
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
|
Rajni
|
Nagaraju
|
Rajni
|
Rajni
|
Shivam
|
Mahesh
|
Shivam
|
Mahendra
|
Mahesh
|
Sanjay
|
Mahesh
|
Shivam
|
Sai
|
0 comments :
Post a Comment