JOIN In SQL

SQL JOIN clause is used to work with  two or more tables, it combine rows from two or more tables, based on a common field between them.By using Joins we can fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join.

Database Design For JOIN

create table Employee(

id int identity(1,1) primary key,

Username varchar(50),

FirstName varchar(50),

LastName varchar(50),

DepartID int

)

create table Departments(

id int identity(1,1) primary key,

DepartmentName varchar(50)

)

after creating the table design of two tables insert data according to your need. 

Different Types Of JOINS 

Inner JOIN

Inner JOIN is used when we need to retrieve only same record from both tables .It returns only the matched record from both tables. 

Example:

select e.UserName, e.FirstName,e.LastName from Employee e inner join Departments d on e.DepartId=d.id

Or

select * from Employee e inner join Departments d on e.DepartId=d.id

Outer JOIN

Outer join returns all the rows of both tables whether it has matched or not. Outer JOIN's has three types

1. Left Outer JOIN

2. Right Outer JOIN

3. Full Outer JOIN

Left Outer JOIN

Left outer join returns all records(rows) from left table and from right table it returns only matched records. If there are no columns matching in the right table, it returns NULL values.

Example:

select * from Employee e left outer join Departments d on e.DepartId=d.id

Right Outer JOIN

Right outer join just returns all records(rows) from right table and from left table it returns only matched records. If there are no columns matching in the left table, it returns NULL values.
Example:

select * from Employee e right outer join Departments d on e.DepartId=d.id

Full Outer JOIN

Full outer join combines left outer join and right outer join. This join returns all records from both the tables.If there are no columns matching in the both tables, it returns NULL values.

Example:

select * from Employee e full outer join Departments d on e.DepartId=d.id

Except these two other JOIN's SQL supports but these JOIN not used in daily practice

Self JOIN

In a self join we are joining the same table to itself by essentially creating two copies of that table.When we do a self join, the table names absolutely must use aliases otherwise the column names would be ambiguous.

Example:

select e1.Username as Profile_Name, e2.FirstName as Name from Employee e1 inner join Employee e2 on e1.FirstName=e2.LastName

Cross JOIN

It returns a set of records  which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product. If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

Example:

Select * from Employee cross join Departments