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.
