SQL Joins

SQL Join

SQL join is used to get the data from two or more table in the database.

A relation between the table is maintained by using the PK and FK reference.

 We can retrieve the data from two or more table using joins which containg some common values in both the table.

SQL join types

Inner join -  Retrieve the data from two or more table when there is matching records in both the table.

select a.FirstName,a.LastName, b.OrderID,b.ShipName,b.CustomerID

from

Employees a

inner join

Orders b

on

a.EmployeeID = b.EmployeeID

order by a.FirstName

Left join -  Retrive all the data from left table with matching records on right table

select a.FirstName,a.LastName, b.OrderID,b.ShipName,b.CustomerID

from

Employees a

left join

Orders b

on

a.EmployeeID = b.EmployeeID

order by a.FirstName

Right join – Retrive all the data from right table with matching records on left table.

select a.FirstName,a.LastName, b.OrderID,b.ShipName,b.CustomerID

from

Employees a

right join

Orders b

on

a.EmployeeID = b.EmployeeID

order by a.FirstName

Full join -  retrive data when there is matching records in one of the table.

select a.FirstName,a.LastName, b.OrderID,b.ShipName,b.CustomerID

from

Employees a

full join

Orders b

on

a.EmployeeID = b.EmployeeID

order by a.FirstName

Self join - is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Cartesian join - returns the Cartesian product of the sets of records from the two or more joined tables.