|
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.
|
