---
title: "Types Of Join in Sql Server"  
description: "Join is use to combine more than two table to find result as per our need.Join is possible when there is one match in joining table."  
author: "Manish Kumar"  
published: 2017-01-17  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/12231/types-of-join-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Types Of Join in Sql Server

##

Join is use to [combine](https://yourviews.mindstick.com/view/88480/why-developers-combine-multiple-ai-tools-instead-of-one) more than two table to find result as per our need.Join is possible when there is one match in joining table.\

##### Types of join

1-[Inner Join](https://www.mindstick.com/blog/304465/difference-between-inner-join-left-join-right-join-and-full-outer-join-in-sql-server)

2-Left Join

3-Right Join

4-[Full Join](https://www.mindstick.com/forum/162063/what-is-the-difference-between-inner-join-left-join-right-join-full-join)

5-[Self Join](https://www.mindstick.com/forum/33686/what-is-a-self-join-in-sql-server)

6-Cartesian Join or [Cross Join](https://www.mindstick.com/interview/1850/what-is-cross-join)

Inner Join Inner Join is very important Join most of the time we use inner join.When we join two or more table with inner join it returns all the rows when there is a match in both the table.

We have two table [Employee](https://www.mindstick.com/articles/178134/how-to-write-the-best-welcome-letter-for-new-employees) and I have inserted few records in it.

\

![Types Of Join in Sql Server](https://www.mindstick.com/mindstickarticle/f35dfe09-0ce8-4351-a7d1-fd1a19740316/images/81c299b4-3574-4b85-bc2a-371d08a2a734.png)\

E_Salary

![Types Of Join in Sql Server](https://www.mindstick.com/mindstickarticle/f35dfe09-0ce8-4351-a7d1-fd1a19740316/images/7b75f0e4-235c-4c7e-9d1f-2effc4b8670d.png)\

```
select
Employee.Name,Employee.Department,E_Salary.Salaryfrom
Employeeinner joinE_SalaryonEmployee.Id=E_Salary.S_Id
```

![Types Of Join in Sql Server](https://www.mindstick.com/mindstickarticle/f35dfe09-0ce8-4351-a7d1-fd1a19740316/images/0f6ae5e1-d669-40c8-aa38-8942fb14381d.png)\

**Left Join****-**When we Join more than two table with left join it gives all record from the left table and matching record form the right table.It means when right table matches null record or 0 record then right table return 0 record.

```
select
Employee.Name,Employee.Department,E_Salary.Salaryfrom
Employeeleft joinE_SalaryonEmployee.Id=E_Salary.S_Id
```

##### Output

![Types Of Join in Sql Server](https://www.mindstick.com/mindstickarticle/f35dfe09-0ce8-4351-a7d1-fd1a19740316/images/ae720087-7b2f-49c1-8c88-745028432d11.png)\

Right Join-When we join more than [two tables](https://www.mindstick.com/forum/159646/how-to-join-two-tables-in-oracle-to-get-single-line-results) with right join.It gives all record from the right table and matching from the left table.Right Join is the opposite of Left Join.If there is 0 records in the left table then left join [return null](https://www.mindstick.com/forum/34356/facebook-email-field-return-null-even-if-the-email-permission-is-set-and-accepted) records.

```
select
Employee.Name,Employee.Department,E_Salary.Salaryfrom
EmployeeRight joinE_SalaryonEmployee.Id=E_Salary.S_Id
```

Output

![Types Of Join in Sql Server](https://www.mindstick.com/mindstickarticle/f35dfe09-0ce8-4351-a7d1-fd1a19740316/images/2a5aa9ee-070a-4487-820e-056c2ba8e1f8.png)\

\

**Full Join**-When we use Full Join for combining record .It return all the record from the left table and all the records from the right table and give null for missing records.

```
select
Employee.Name,Employee.Department,E_Salary.Salary
from
Employee
full join
E_Salary
on
Employee.Id=E_Salary.S_Id
```

##### Output

![Types Of Join in Sql Server](https://www.mindstick.com/mindstickarticle/f35dfe09-0ce8-4351-a7d1-fd1a19740316/images/d3700f25-13dc-4334-9861-cec92086793e.png)\

##### Self Join

Self Join is use to [join the table](https://www.mindstick.com/forum/157565/how-many-ways-to-join-the-table-in-mysql-explain-with-examples) by itself by making its alias just like [virtual table](https://www.mindstick.com/interview/876/which-virtual-table-does-a-trigger-use).

```
select a.Name,b.DepartmentfromEmployee a,Employee bwherea.Id=b.Id
```

Output

![Types Of Join in Sql Server](https://www.mindstick.com/mindstickarticle/f35dfe09-0ce8-4351-a7d1-fd1a19740316/images/8c827993-e997-4e42-81be-3145c5978245.png)\

\

**Cartesian Join**- When we join two or more table with cross join then it return cross product of the tables.It is also known as cross join.

Suppose you have table1 with m rows and n columns then the result o cross join is m*n rows.

```
SELECT * FROM
Employee CROSS JOIN E_Salary;
```

## \

## Output

## \

```

```

---

Original Source: https://www.mindstick.com/articles/12231/types-of-join-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.