Joins in sql server.

JOIN

A SQL join clause combines records from two or more tables in a relational database.
generally tables are related to each other using foreign key constraints.A join is a means for combining fields from two tables (or more) by using values common to each.

 There are different types of JOINS In SQL server,
1. CROSS JOIN
2. INNER JOIN 
3. OUTER JOIN 

Outer Joins are again divided into 3 types
1. Left Join or Left Outer Join
2. Right Join or Right Outer Join
3. Full Join or Full Outer Join


Now let's understand all the JOIN types, with examples and the differences between them. 
Step-1 Here I Created two table Employee and Department

Create table Employee
(
     EmpID int primary key,
     EmpName nvarchar(50),
     EmpGender nvarchar(50),
     EmpSalary int,
     EmpDepartmentId int foreign key references Department(EmpID)
)

Create table Department
(
     EmpID int primary key,
     EmpDepartmentName nvarchar(50),
     EmpLocation nvarchar(50),
     EmpDepartmentHead nvarchar(50)
)

Step-2 Insert data into both table

Insert into Department values (1, 'IT', 'Delhi', 'Rampal'),
(2, 'Payroll', 'Hasanpur', 'Dev'),
(3, 'HR', 'USA', 'John'),

(4, 'Other Department', 'Agra', 'Dilip')

Now insert into Employee

Insert into Employee values (1, 'Ravi', 'Male', 6000, 1),
(2, 'Seema', 'Female', 4000, 3),
 (3, 'Shyam', 'Male', 2500, 1),
 (4, 'Sameer', 'Male', 5500, 2),
 (5, 'Neeraj', 'Male', 3800, 2),
 (6, 'Rahul', 'Male', 9000, 1),
 (7, 'Pinki', 'Female', 3800, 3),
 (8, 'Bhavna', 'Female', 4500, 1),
 (9, 'Narendra', 'Male', 8500, NULL),

(10, 'Dev', 'Male', 7800, NULL)

Now Here we have two table

Syntax of join

SELECT ColumnList  FROM  Table1
JOIN_TYPE  Table2

ON  JoinCondition

1. CROSS JOIN
 Cross Join shouldn't have ON clause. it's produces the cartesian product of the two tables involved in the join. Now we have two table first is 
Employee table that have 10 rows and in the Department table we have 4 rows. So, a cross join between these 2 tables produces 40 rows. 
like below.

SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee

CROSS JOIN Department
After Execute the above query output will be:

 


2. INNER JOIN or JOIN
INNER JOIN, returns only the matching rows between both the tables and eliminated Non matching rows.
I want to retrieve EmpName, EmpGender, EmpSalary and EmpDepartmentName from Employee and Department table.

SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee
INNER JOIN Department

ON Employee.EmpDepartmentId=Department.EmpID
OR
SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee
JOIN Department

ON Employee.EmpDepartmentId=Department.EmpID

The output of the above query should be as shown below.

Here we got only 8 rows, but in the Employee table, we have 10 rows. We didn't get Narendar and dev records. This is because the EMPDEPARTMENTID, in Employee table is NULL for these two employees and doesn't match with EmpID column in Department table.

LEFT JOIN or LEFT OUTER JOIN
LEFT JOIN, returns all the matching rows and  non matching rows from the left table. In reality, INNER JOIN and LEFT JOIN are extensively used.You can use, LEFT JOIN or LEFT OUTER JOIN. OUTER keyowrd is optional
I want all the rows from the Employees table, including Narendar and dev 
records.

SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee
LEFT OUTER JOIN Department

ON Employee.EmpDepartmentId=Department.EmpID
OR
SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee
LEFT  JOIN Department

ON Employee.EmpDepartmentId=Department.EmpID

The output of the above query should be as shown below.

RIGHT JOIN or RIGHT OUTER JOIN 
RIGHT JOIN, returns all the matching rows and non matching rows from the right table.You can use, RIGHT JOIN or RIGHT OUTER JOIN. OUTER keyowrd is optional.
Let's say I want, all the rows from the right table. 
SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee
RIGHT OUTER JOIN Department

ON Employee.EmpDepartmentId=Department.EmpID
OR
SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee
RIGHT JOIN Department

ON Employee.EmpDepartmentId=Department.EmpID

The query output should be, as shown below. 

FULL JOIN or FULL OUTER JOIN
FULL JOIN, returns all rows from both the left and right tables, including the non matching rows.You can use, FULLJOIN or FULL OUTER JOIN. OUTER keyowrd is optional
Let's say I want all the rows from both the tables involved in the join.
SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee
FULL OUTER JOIN Department

ON Employee.EmpDepartmentId=Department.EmpID
OR
SELECT EmpName, EmpGender, EmpSalary, EmpDepartmentName
FROM Employee
FULL JOIN Department


ON Employee.EmpDepartmentId=Department.EmpID

 The query output should be, as shown below.