Join

Join clause is used to combine data or rows from two or more tables based 
on a common column between them.

Types of Joins:

INNER JOIN 

LEFT OUTER JOIN / LEFT JOIN 

RIGHT OUTER JOIN / RIGHT JOIN 

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

INNER JOIN: Returns all rows when matching rows from the both tables.

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table, those records are not 
matched then such records showing as NULL (Right Table)

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table, those records are not 
matched then such records showing as NULL (left Table)

FULL JOIN: Return all rows from left & Right Table, matching rows and not matching Records.
Full Join Result =INNER JOIN + LEFT JOIN + RIGHT JOIN

CROSS JOIN: Cross join is a cartesian join means cartesian product of both the tables. 
This join does not need any condition to join two tables. 
This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table.

Select * from table_1 cross join table_2 

SELF JOIN: SELF JOIN is used to join a table to itself.

Query:

Create table Department 

(

DepartmentId int identity(1,1) Primary Key,

Name varchar(50) Not Null

)

Create table Employee

(

Id int identity(1,1) Primary Key,

FirstName varchar(50) Not Null,

LastName varchar(50) Not Null,

City varchar(50) Not Null,

AadharcardNumber BIGINT Not Null,

DepartmentId int null Foreign Key references Department(DepartmentId)

)

Insert Into Department(Name) values ('Account')

Insert Into Department(Name) values ('IT')

Insert Into Department(Name) values ('Finance')

Insert Into Department(Name) values ('Personnel')

Insert Into Department(Name) values ('Admin')

Insert Into Employee (FirstName,LastName,City,AadharcardNumber,DepartmentId) 

values 

('Manish','Sonawane','Pune',121232565545,1),

('Ganesh','Khedkar','Sangli',121212125559,2),

('Pravin','pawar','Pune',653212312123,2),

('Ghanshyam','Kale','Satara',545456454555,3),

('Amol','solanki','Nasik',675675674544,4),

('Nitin','Sonawane','Nagpur',435453453453,4),

('Sagar','Aher','Nasik',675685674544,2),

('Govind','Kumar','Nasik',656887674544,1)

Select * from Department

Select * from Employee

Inner Join

Select * from Employee Inner join Department 

on Employee.DepartmentId=Department.DepartmentId

Left Join

Select * from Employee LEFT join Department 

on Employee.DepartmentId=Department.DepartmentId

Right Join

Select * from Employee Right join Department 

on Employee.DepartmentId=Department.DepartmentId

FULL Join

Select * from Employee FULL join Department 

on Employee.DepartmentId=Department.DepartmentId

Cross Join

Select * from Employee Cross join Department 

Summary:

Inner join = matched Records

Left Join = All records from left Side 

matched - we will get

non matched Records = NULL Value

Right join = All records from Right Side

matched - we will get

non matched Records = NULL Value

Full = matched + Non matched Rows 

= Inner Join + LEFT join + Right Join

Cross join = Cartesian Join = A B =9 X 5 =45 Rows