SQL Joins
There are four basic types of SQL joins: inner, left, right, and full. The easiest and most intuitive way to
explain the difference between these four types is by using a Venn diagram, which shows all possible
logical relations between data sets.
Let’s say we have two sets of data in our relational database: table A and table B, with some sort of
relation specified by primary and foreign keys. The result of joining these tables together can be visually
represented by the following diagram:
INNER JOIN
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Join over two tables
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the
matched records from the right table (table2). The result is NULL from the right
side, if there is no match.
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and
the matched records from the left table (table1). The result is NULL from the
left side, when there is no match.
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Example
B
AB
Inner join Query
SELECT A.A_Name,B.B_Subject
FROM AB
INNER JOIN
A
ON
A.A_ID = AB.A_ID
INNER JOIN
B
ON
AB.B_ID = B.B_ID
Retrieved Result
Inner join Query
SELECT A.A_Name,B.B_Subject
FROM A
INNER JOIN
AB
ON
A.A_ID = AB.A_ID
INNER JOIN
B
ON
AB.B_ID = B.B_ID
Retrieved Result
Inner join Query
SELECT A.A_Name,B.B_Subject
FROM B
INNER JOIN
AB
ON
AB.B_ID = B.B_ID
INNER JOIN
A
ON
AB.A_ID = A.A_ID
Retrieved Result
Left join Query
SELECT A.A_Name,AB.B_ID
FROM A Left JOIN
AB ON AB.A_ID = A.A_ID
Retrieved Result
Left join with inner join Query
SELECT A.A_Name,AB.B_ID
FROM A
Left JOIN
AB
ON
AB.A_ID = A.A_ID
Inner join
B ON B.B_ID = AB.B_ID
Retrieved Result
Inner join with left join Query
SELECT A.A_Name,AB.B_ID
FROM A
inner JOIN
AB
ON
AB.A_ID = A.A_ID
left join
B ON B.B_ID = AB.B_ID
Retrieved Result
Left join with left join Query
SELECT A.A_Name,AB.B_ID
FROM A
Left JOIN
AB
ON
AB.A_ID = A.A_ID
left join
B
ON
B.B_ID = AB.B_ID
Retrieved Result
Inner join with right join
SELECT A.A_Name,AB.B_ID
FROM A
inner JOIN
AB
ON
AB.A_ID = A.A_ID
right join
B ON B.B_ID = AB.B_ID
Retrieved Result
Right join with inner join
SELECT A.A_Name,AB.B_ID
FROM A
right JOIN
AB
ON
AB.A_ID = A.A_ID
inner join
B
ON
B.B_ID = AB.B_ID
Retrieved Result
Right join with right join query
SELECT A.A_Name,AB.B_ID,B_Subject
FROM A
right JOIN
AB
ON
AB.A_ID = A.A_ID
right join
B ON B.B_ID = AB.B_ID
Retrieved Result
SELECT A.A_Name,AB.B_ID
FROM A
Left JOIN
AB
ON
AB.A_ID = A.A_ID
where AB.A_ID IS NULL
SELECT AB.A_ID,B.B_Subject
FROM AB Right JOIN
B ON AB.B_ID = B.B_ID
where AB.A_ID IS NULL
