Home > DeveloperSection > Blogs > SQL Joins

SQL Joins


MSSQL Server SQL Server  Join 
Ratings:
1 Comment(s)
 686  View(s)
Rate this:

SQL Joins

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.

Left outer join

Right outer join

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.

Example

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

 



Very nice blog

By Kamlakar Singh on   one year ago
helpful for learn join in sql

Don't want to miss updates? Please click the below button!

Follow MindStick