Hack 26. Include the Rows Your JOIN Forgot

In a one-to-many relationship, a join condition may make some rows invisible in the output from a query. Here's a simple way to make them visible again using an OUTER JOIN.

Sometimes a JOIN seems to lose things that should be there. Surprisingly, this is normal and desired behavior, and fortunately, there's a way to get exactly what you are looking for. Suppose you have a database of customers, shown in Table 5-4, and invoices, shown in Table 5-5. You need to write a query to return the number of invoices in the database for each customer. Some customers haven't ordered anything yet, and for them the count should be 0.

Table 5-4. The customer table

ID	Name
1	Betty
2	Robert
3	Janette

Table 5-5. The invoice table

Invoiceno	Whn	Custid	Cost
1	2006-11-01	1	100
2	2006-11-05	1	500
3	2006-11-11	3	200

Consider the following query:

SELECT name,COUNT(*)
 FROM customer JOIN invoice on (id=custid)
 GROUP BY name

Here are the results:

Name COUNT(*)
------------------------
Betty 2
Janette 1

The JOIN has restricted the result to cases where there is at least one entry for a customer in invoice.

LEFT OUTER JOIN

By default, a JOIN is an INNER JOIN. This will give only the rows that match both tables:

mysql> SELECT *
 -> FROM customer INNER JOIN invoice
 -> ON (customer.id=custid);
+----+---------+-----------+------------+--------+--------+
| id | name | invoiceNo | whn | custid | cost |
+----+---------+-----------+------------+--------+--------+
| 1 | Betty | 1 | 2006-11-01 | 1 | 100.00 |
| 1 | Betty | 2 | 2006-11-05 | 1 | 500.00 |
| 3 | Janette | 3 | 2006-11-11 | 3 | 200.00 |
+----+---------+-----------+------------+--------+--------+

 

In contrast, a LEFT OUTER JOIN will include all of the rows in the left tableeven if they do not match anything on the right:

mysql> SELECT *
 -> FROM customer LEFT OUTER JOIN invoice
 -> ON (customer.id=custid);
+----+---------+-----------+------------+--------+--------+
| id | name | invoiceNo | whn | custid | cost |
+----+---------+-----------+------------+--------+--------+
| 1 | Betty | 1 | 2006-11-01 | 1 | 100.00 |
| 1 | Betty | 2 | 2006-11-05 | 1 | 500.00 |
| 2 | Robert | NULL | NULL | NULL | NULL |
| 3 | Janette | 3 | 2006-11-11 | 3 | 200.00 |
+----+---------+-----------+------------+--------+--------+

 

You can use the phrase LEFT JOIN in place of LEFT OUTER JOIN. The word OUTER is optional.

Similarly, if you are using an inner join you can make that explicit by using INNER JOIN in place of JOIN. This is mandatory in Access.

Filtering must be done in the join condition (the ON clause). In SQL, the JOIN clause is performed first, and then the WHERE clause is applied.

If you want to get a count of 0 for Robert you need a LEFT OUTER JOIN so that every row of the table on the left (customer) is included:

SELECT name,count(*)
 FROM customer LEFT JOIN invoice on (id=custid)

Now, Robert is included even though he has no invoices:

Name count(*)
------------------------
Betty 2
Robert 0
Janette 1

You can also solve this problem by using a UNION, and writing a second query that returns 0 for customers who are not in invoice:

SELECT name,count(*)
 FROM customer JOIN invoice ON (id=custid)
UNION
SELECT name,0
 FROM customer
 WHERE id NOT IN (SELECT custid FROM invoice)