Using JOINS In SQL Server

JOINS are used to retrieve data to two or more related tables.  In this section I will show you the different types of joins.

Types of Joins used in Sql Server:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• CROSS JOIN

Here is the syntax to perform the different types of joins. 
I hope you find this helpful.

USING JOINS IN SQL SERVER

Create table tblEmployee3

(

ID int primary key NOT NULL,

Name nvarchar(50),

Gender nvarchar(50),

Salary int NULL,

DepartmentID int NOT NULL,

)

Create table tblDepartment3

(

DepartmentID int NOT NULL,

DepartmentName nvarchar(50),

Location nvarchar(50),

DepartmentHead nvarchar(50) NULL,

)

select * from tblEmployee3

select * from tblDepartment3

--Using the JOIN clause to JOIN two tables--

--INNER JOIN--

select Name, Gender, Salary, DepartmentName

from tblEmployee3

INNER JOIN tblDepartment3

ON tblEmployee3.DepartmentID = tblDepartment3.DepartmentID

--LEFT JOIN--

--LEFT JOIN returns all matching rows + non matching rows from the left table--

select Name, Gender, Salary, DepartmentName

from tblEmployee3

LEFT JOIN tblDepartment3

ON tblEmployee3.DepartmentID = tblDepartment3.DepartmentID

--OR LEFT OUTER JOIN, WHICH IS THE SAME AS LEFT JOIN--

select Name, Gender, Salary, DepartmentName

from tblEmployee3

LEFT JOIN tblDepartment3

ON tblEmployee3.DepartmentID = tblDepartment3.DepartmentID

--RIGHT JOIN OR RIGHT OUTER JOIN--

--RIGHT JOIN returns all matching rows + non matching from the right side

select * from tblEmployee3

select * from tblDepartment3

select Name, Gender, Salary, DepartmentName

from tblEmployee3

RIGHT OUTER JOIN tblDepartment3

ON tblEmployee3.DepartmentID = tblDepartment3.DepartmentID

--FULL JOIN--

--WILL GET ALL RECORDS FROM THE LEFT AND RIGHT--

select * from tblEmployee3

select * from tblDepartment3

select Name, Gender, Salary, DepartmentName

from tblEmployee3

FULL OUTER JOIN tblDepartment3

ON tblEmployee3.DepartmentID = tblDepartment3.DepartmentID

--CROSS JOIN--

--WILL NOT HAVE AN ON CLAUSE--

--WILL TAKE EACH RECORD FROM THE RIGHT TABLE AND ASSOCIATE EVERY RECORD IN EMPLOYEES TABLE--

select * from tblEmployee3

select * from tblDepartment3

select Name, Gender, Salary, DepartmentName

from tblEmployee3

CROSS JOIN tblDepartment3