|
It seems to be a popular misconception that the number of rows returned by a left
outer join must match the number of rows in the left table. In this article, I will
explain the inner workings of a SQL outer join to clear such misconceptions. Enjoy!
– Pradeep
Any type of SQL join effectively multiplies the rows in the left table with the
rows in the right table. An INNER JOIN acts as a filter that shows
only those rows where the key fields have the same value. AN OUTER JOIN
is used to include the rows that are "missing" in an inner join.
Consider the following two tables - Customers and Orders.
|
CustomerID
|
Name
|
|
5001
|
Clark Kent
|
|
5002
|
Peter Parker
|
|
5003
|
Bruce Banner
|
|
|
OrderID
|
CustomerID
|
Quantity
|
|
10001
|
5001
|
5
|
|
10002
|
5003
|
10
|
|
10003
|
5001
|
3
|
|
Now, let's run the following query that performs a left outer join:
|
|
SELECT Name, Quantity
FROM ( [Customers] LEFT OUTER JOIN [Orders]
ON [Customers].[CustomerID] = [Orders].[CustomerID])
|
Here is the resulting table:
|
Name
|
Quantity
|
|
Clark Kent
|
3
|
|
Clark Kent
|
5
|
|
Peter Parker
|
(null)
|
|
Bruce Banner
|
10
|
|
There are two things worth noting here:
-
Peter Parker does not have any associated quantity. This is because
Peter Parker never placed any order. For any unmatched values, LEFT
OUTER JOIN places a (null) value for the selected fields
from the right table.
-
The number of rows in the result are more than the number of rows in Customers
table
The number of resulting rows is a function of two variables a) the number of rows
in the left table and b) whether the join condition results in a one-to-one match
or a one-to-many match for each individual row of the left table.
If a row from the left table matches just one row in the right table(or does not
match any row), it contributes towards one row in the resulting table.
If a row from the left table matches n number of rows in the right
table, it contributes toward n rows in the resulting table.
Essentially, the number of rows in the resulting table will be either equal to or
greater than the number of rows in the left table.
A RIGHT OUTER JOIN is simliar to a LEFT OUTER JOIN except
the roles of the tables are reversed. For any unmatched value from right
to left, (null) values are placed in the selected fields of
the left table.
A FULL OUTER JOIN fills (null) values for unmatched left-to-right
and unmatched right-to-left rows.
Let us look at a couple of cases where outer joins are very useful.
Identify missing information
The following query identifies all the customers who have not placed any orders.
|
|
SELECT Name
FROM ( [Customers] LEFT OUTER JOIN [Orders]
ON [Customers].[CustomerID] = [Orders].[CustomerID])
WHERE [Orders].[Quantity] IS NULL
|
Creating aggregate data reports
The following query reports the number of orders placed by each customer:
|
|
SELECT CustomerID, COUNT([Orders].[OrderID])
FROM ( [Customers] LEFT OUTER JOIN [Orders]
ON [Customers].[CustomerID] = [Orders].[CustomerID])
GROUP BY [Customers].[CustomerID]
|
Hope this gives you a better picture on how outer joins work.
|
