SQL

SQL Tutorial

SQL Server Joins

SQL Joins combine records from multiple tables:

The following tables are used to explain the concepts of joins

Product:

ProductID	ProductName	Color	Price
1	Bicycle	Red	1000
2	Motorbike	Blue	10000
3	Car	Green	20000
4	Computer	Black	20000

Orders:

OrderID	ProductID	CustomerID	OrderQuantity	UnitPrice	Discount	Total Amount
1	4	1	1	20000	2000	18000
2	2	2	1	10000	1000	9000

Employee:

EmpID	Firstname	Lastname	Title	ManagerID
888	Kevin	Brown	President	Null
889	Peter	Haynes	GM	888
890	Ben	Williams	Supervisor	889
891	John	Smith	Clerk	890

Inner Join: Returns common records from both the tables

SELECT p.ProductID,p.ProductName,o.OrderID
FROM Product p inner join Orders o on p.ProductID=o.ProductID

ProductID	ProductName	OrderID
2	Motorbike	2
4	Computer	1

Left join: Returns all rows from the left table and matching rows from the right table
SELECT p.ProductID,p.ProductName,o.OrderID,o.ProductID
FROM Product p left join Orders o on p.ProductID=o.ProductID

ProductID	ProductName	OrderID	ProductID
1	Bicycle	Null	Null
2	Motorbike	2	2
3	Car	Null	Null
4	Computer	1	4

Right join: Returns all rows from the right table and matching rows from the left table
SELECT p.ProductID,p.ProductName,o.OrderID,o.ProductID
FROM Product p right join Orders o on p.ProductID=o.ProductID

ProductID	ProductName	OrderID	ProductID
4	Computer	1	4
2	Motorbike	2	2

Full join: Returns all rows from both the tables
SELECT p.ProductID,p.ProductName,o.OrderID,o.ProductID
FROM Product p full join Orders o on p.ProductID=o.ProductID

ProductID	ProductName	OrderID	ProductID
1	Bicycle	Null	Null
2	Motorbike	2	2
3	Car	Null	Null
4	Computer	1	4

Cross join: Returns the cartesian product
SELECT p.ProductID,p.ProductName,o.OrderID,o.ProductID
FROM Product p, Orders o

ProductID	ProductName	OrderID	ProductID
1	Bicycle	1	4
2	Motorbike	1	4
3	Car	1	4
4	Computer	1	4
1	Bicycle	2	2
2	Motorbike	2	2
3	Car	2	2
4	Computer	2	2

Self join: Joins the table to itself
SELECT
e1.Empid
,e1.FirstName
,e1.LastName
,e1.Title
,e2.FirstName as ManagerFirstName
,e2.LastName as ManagerLastName
FROM Employee e1, Employee] e2
Where e1.Managerid=e2.Empid

EmpID	Firstname	Lastname	Title	ManagerFirstName	ManagerLastName
889	Peter	Haynes	GM	Kevin	Brown
890	Ben	Williams	Supervisor	Peter	Haynes
891	John	Smith	Clerk	Ben	Williams

As the President don’t have any manager, the record doesn’t show up. To show that record the following query can be used
SELECT Empid
,FirstName
,LastName
,Title
,Null as ManagerFirstName
,Null as ManagerLastName
FROM Employee
WHERE [Managerid] is null
Union
SELECT
e1.Empid
,e1.FirstName
,e1.LastName
,e1.Title
,e2.FirstName as ManagerFirstName
,e2.LastName as ManagerLastName
FROM Employee e1,Employee e2
Where e1.Managerid=e2.Empid

EmpID	Firstname	Lastname	Title	ManagerFirstName	ManagerLastName
888	Kevin	Brown	President	Null	Null
889	Peter	Haynes	GM	Kevin	Brown
891	Ben	Williams	Supervisor	Peter	Haynes
892	John	Smith	Clerk	Ben	Williams