In this blog, I’m explaining about joins in SQL

SQL joins are used to combine rows from two  or more tables.

Types of joins

1 .Inner joins

2. Outer joins

3. Self joins

Inner join

The join  that display  only  the rows that have match in both the joined  tables is known  as inner join.

Example

select ed.id, ed.firstName,ed.RoleId,rt.roleName from EmployeeDetail ed
inner join RoleTable rt on ed.RoleId=rt.roleId

Different types of inner joins

• Equi join
• Cross join

Equi Join

The Equi join is used to display all the matched records from the joined tables. In this join we need to use * sign to join the table.

Example

select * from EmployeeDetail ed
 inner join RoleTable rt on ed.RoleId=rt.roleId

Cross join

A cross join that produces Cartesian product of the tables that involved in the join. The size  of a  Cartesian product  is the  number of the  rows in the first table multiplied by the number of rows in the  second table.

Example

select * from EmployeeDetail
cross  join RoleTable

Outer joins

A join that  return  all the  rows that  satisfy  the condition and unmatched rows in the joined table is  an outer join.

1. Left outer join
2. Right outer join
3. Full outer join

Left outer join

The left  outer join  displays all  the rows from the first table and matched rows from  the second table.

Example

select * from EmployeeDetail ed
left outer join RoleTable rt on ed.RoleId=rt.roleId

Right outer join

The right outer join  displays all  the rows from the second table and matched rows from  the first table.

select * from EmployeeDetail ed
right outer join RoleTable rt on ed.RoleId=rt.roleId

Full outer join

Full outer join  displays all  matching and non matching rows of both the tables.

Example

select * from EmployeeDetail ed
full outer join RoleTable rt on ed.RoleId=rt.roleId

Self  join         

Joining  the table  itself called  self join. Self join  is used to retrieve the

records having  some relation or similarity with other records in the same

table.

Example

select ed.FirstName,rt.RoleName as 'Assign role' from EmployeeDetail ed
inner join RoleTable rt on ed.RoleId=rt.roleId