Marvellous Coder

Join Allow us to combine mutltiple tables based on relationships and return desiger results.

We will use two table as bellow for example.

Table-1:tblEmployee

EmpId	Name	managerid
1	Vikash
2	Nilesh	1
3	Prabhat	1

Table-2:tblSalary

TranId	EmpId	Month	Salary
1	1	Jan-2024	5000
2	3	Jan-2024	4000
3	4	Jan-2024	3000

Joins are available in Sql Server as below:

1- Inner Join

Inner join return results if both table match records with common key.

Example:

select E.EmpId,E.Name,S.Month,S.Salary from tblEmployee E
inner join tblSalary S on E.EmpId=S.EmpId

Output:

EmpId	Name	Month	Salary
1	Vikash	Jan-2024	5000.00
3	Prabhat	Jan-2024	4000.00

As per above out put, there are two rows return by inner join.if you check returned empid are available in both tables.

2- Left Join

LEFT JOIN returns all records from the first table(left table), even if there are no matches in the right table.

Example:

select E.EmpId,E.Name,S.Month,S.Salary from tblEmployee E
left join tblSalary S on E.EmpId=S.EmpId

Output:

EmpId	Name	Month	Salary
1	Vikash	Jan-2024	5000.00
2	Nilesh	NULL	NULL
3	Prabhat	Jan-2024	4000.00

As per above out put, you will get all records from tblEmployee. even there some records are not available in table tblsalary.

3- Right Join

Right JOIN returns all records from the Second table(Right table), even if there are no matches in the Left table. This join will return only those records which are available in both table or all records from right table.

Example:

select E.EmpId,E.Name,S.Month,S.Salary from tblEmployee E
right join tblSalary S on E.EmpId=S.EmpId

Output:

EmpId	Name	Month	Salary
1	Vikash	Jan-2024	5000.00
3	Prabhat	Jan-2024	4000.00
NULL	NULL	Jan-2024	3000.00

As per above out put, you will get all records from tblSalary. even there some records are not available in table tblEmployee.

4- Full Outer Join

Full Outer Join returns all records from the both table, even if there are no matches in tables.

Example:

select E.EmpId,E.Name,S.Month,S.Salary from tblEmployee E
full outer join tblSalary S on E.EmpId=S.EmpId

Output:

EmpId	Name	Month	Salary
1	Vikash	Jan-2024	5000.00
2	Nilesh	NULL	NULL
3	Prabhat	Jan-2024	4000.00
NULL	NULL	Jan-2024	3000.00

As per above out put, you will get all records from both tables.

5- Self Join

Self join is when we join a table with itself. In below query we are going to get manager Name with Employee. Manager is also a employee so we need to use a self join to get records.

Example:

select E.EmpId,E.Name,M.Name Manager from tblEmployee E, tblEmployee M where E.Managerid=M.EmpId

Output:

EmpId	Name	Manager
2	Nilesh	Vikash
3	Prabhat	Vikash

6- Cross Join

Cross join returns the Cartesian of the records from both tables.

Example:

select E.EmpId,E.Name,S.Month,S.Salary from tblEmployee E, tblEmployee M cross join tblSalary S

Output:

EmpId	Name	Month	Salary
1	Vikash	Jan-24	5000
1	Vikash	Jan-24	4000
1	Vikash	Jan-24	3000
1	Vikash	Jan-24	5000
1	Vikash	Jan-24	4000
1	Vikash	Jan-24	3000
1	Vikash	Jan-24	5000
1	Vikash	Jan-24	4000
1	Vikash	Jan-24	3000
2	Nilesh	Jan-24	5000
2	Nilesh	Jan-24	4000
2	Nilesh	Jan-24	3000
2	Nilesh	Jan-24	5000
2	Nilesh	Jan-24	4000
2	Nilesh	Jan-24	3000
2	Nilesh	Jan-24	5000
2	Nilesh	Jan-24	4000
2	Nilesh	Jan-24	3000
3	Prabhat	Jan-24	5000
3	Prabhat	Jan-24	4000
3	Prabhat	Jan-24	3000
3	Prabhat	Jan-24	5000
3	Prabhat	Jan-24	4000
3	Prabhat	Jan-24	3000
3	Prabhat	Jan-24	5000
3	Prabhat	Jan-24	4000
3	Prabhat	Jan-24	3000

SQL Server Question and answer

Using Row_Number() Function

Using CTE(Common Table Expressions)

Table-1:tblEmployee

Table-2:tblSalary

1- Inner Join

Example:

Output:

2- Left Join

Example:

Output:

3- Right Join

Example:

Output:

4- Full Outer Join

Example:

Output:

5- Self Join

Example:

Output:

6- Cross Join

Example:

Output:

Get In Touch

Quick Links

Popular Links