SQL Server Joins

Joins : SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables.

Types of Joins :In SQL Server we have only three types of joins. Using these joins we fetch the data from multiple tables based on condition.

1. Inner Join

2. Outer Join

3. Cross Join

4. Self Join

Inner Join : Inner join returns only those records/rows that match in both the tables.

Syntax for Inner Join is as

Select * from table_1 as t1

inner join table_2 as t2

on t1.IDcol=t2.IDcol

Examples : Consider the below three tables



Inner Join 

SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price

FROM tblProduct AS t0 INNER JOIN tblOrder AS t1 ON t0.ProductID =t1.ProductID

ORDER BY t1.OrderID

Output :

Inner Join among more than two tables:

SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS Customer

FROM tblProduct AS t0

INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

INNER JOIN tblCustomer AS t2 ON t1.CustomerID = t2.CustID

ORDER BY t1.OrderID

Output :

Inner Join on multiple conditions:

SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS Customer

FROM tblProduct AS t0

INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

INNER JOIN tblCustomer AS t2 ON t1.CustomerID = t2.CustID AND t1.ContactNo = t2.ContactNo

ORDER BY t1.OrderID

Output :

Outer Join :

We have three types of Outer Join.

1.Left Outer Join : Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

Syntax for Left outer Join is as :

Select * from table_1 as t1

left outer join table_2 as t2

on t1.IDcol=t2.IDcol

Examples :

SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price

FROM tblProduct AS t0

LEFT OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

ORDER BY t0.ProductID

Output :

2.Right Outer Join :Right outer join returns all records/rows from right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values.

Syntax for right outer Join is as :

Select * from table_1 as t1

right outer join table_2 as t2

on t1.IDcol=t2.IDcol

Examples :

SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price

FROM tblProduct AS t0

RIGHT OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

ORDER BY t0.ProductID 

Output :

3.Full Outer Join :Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables. If there are no columns matching in the both tables, it returns NULL values.

Syntax for full outer Join is as :

Select * from table_1 as t1

full outer join table_2 as t2

on t1.IDcol=t2.IDcol

Examples :

SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price

FROM tblProduct AS t0

FULL OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

ORDER BY t0.ProductID

Output :

Cross Join : Cross join is a Cartesian join means Cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table.

Syntax for right outer Join is as :

Select * from table_1

cross join table_2

Examples : 

SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price

FROM tblProduct AS t0, tblOrder AS t1

ORDER BY t0.ProductID

Output :

Self Join : SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of SQL join.

Syntax for right outer Join is as :

SELECT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

Examples :

To understand Self Join,

Here I will explain self join with one example for that first design one table and give name as EmployeeDetails in your database as shown below.

Column Name	Data Type	Allow Nulls
EmpId	Int (set Identity=true)	No
EmpName	varchar(50)	Yes
ManagerId	Int	Yes

Once table designed please enter the data in your table that as shown below.

EmpId	EmpName	ManagerId
1	Suresh	0
2	Prasanthi	1
3	Mahesh	1
4	Sai	2
5	Madhav	2
6	Honey	5

Now if I want get the details of Empolyees who are in Manager Position for that we need to write query like this

SELECT DISTINCT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

Output:

EmpId	EmpName
1	Suresh
2	Prasanthi
5	Madhav

Suppose if I want get the details of Empolyees who are having Managers then we need to write query like

SELECT e2.EmpId, e2.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

Output:

EmpId	EmpName
2	Prasanthi
3	Mahesh
4	Sai
5	Madhav
6	Honey

Basic guide to help you decide which type of join to use:

• Use an inner join when you want matching rows from both tables.
• Use a left or right outer join when you want to preserve all rows from one table and return only matching rows from the other.
• Use a left or right outer join with exclusions when you're looking for something in one table that doesn't exist in another table.
• Use a full outer join when you want all the rows from both sides, with nulls where they don't match.
• Use a cross join when you want to create a Cartesian product from two tables.