Types of Joins in Sql Server | Sql Joins

Hi, In this post i will showing all the types of Sql Joins :

SQL Joins are used to bring a single consolidated data from two or more tables.

I will be using Employee and Dept Table to show types of Joins in Sql Server.
Below is the script for both the tables:
---------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dept](
[deptid] [int] NULL,
[deptname] [varchar](500) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
----------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
[EmpDeptID] [int] IDENTITY(1,1) NOT NULL,
[EMpName] [varchar](100) NULL,
[salary] [numeric](18, 0) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
-----------------------------------------------------------------
INSERT [dbo].[dept] ([deptid], [deptname]) VALUES (1, N'IT')
INSERT [dbo].[dept] ([deptid], [deptname]) VALUES (3, N'Accounts')
INSERT [dbo].[dept] ([deptid], [deptname]) VALUES (4, N'Admin')
INSERT [dbo].[dept] ([deptid], [deptname]) VALUES (9, N'Manager')
INSERT [dbo].[dept] ([deptid], [deptname]) VALUES (22, N'President')
GO

------------------------------------------------------------------------------------------------------------

SET IDENTITY_INSERT [dbo].[Employee] ON 
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (1, N'James', CAST(1000 AS Numeric(18, 0)))
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (3, N'Mary', CAST(5000 AS Numeric(18, 0)))
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (4, N'Anthony', CAST(500 AS Numeric(18, 0)))
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (5, N'jacob1', CAST(555 AS Numeric(18, 0)))
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (8, N'Peter', CAST(4500 AS Numeric(18, 0)))
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (9, N'chandan', CAST(500 AS Numeric(18, 0)))
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO
------------------------------------------------------------------------------------------------------------


select * from dept
select * from employee

1. Full Join

It returns all the rows from both the tables. i.e matched and unmatched
for unmatched rows null will be displayed.

SELECT *
FROM employee e
FULL JOIN Dept d ON e.empDeptId = d.Deptid

2. Inner Join

Only Matching Rows will fetched from both the tables.

SELECT *
FROM employee e
INNER JOIN Dept d ON e.empDeptId = d.Deptid

Or

SELECT *
FROM employee e
JOIN Dept d ON e.empDeptId = d.Deptid

3. Left Join

All the rows from the left table will be fetched along with the matching data from the right table for unmatched rows in the right table null will be displayed for that row.

SELECT *
FROM employee e
LEFT JOIN Dept d ON e.empDeptId = d.Deptid

4. Right Join

This just the opposite of the left join
All the rows from the right table will be fetched along with the matching data from the left table for unmatched rows in the left table null will be displayed for that row.

SELECT *
FROM employee e
RIGHT JOIN Dept d ON e.empDeptId = d.Deptid

5. Self Join

using self join on table in sql server