## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# Using a Join to Fill in Holes in a List

## Problem

You want to produce a summary for each of several categories, but some of the categories are not represented in the data to be summarized. Consequently, the summary has missing categories.

## Solution

Create a reference table that lists each category and produce the summary based on a `LEFT` `JOIN` between the list and the table containing your data. Then every category in the reference table will appear in the result, even “empty” ones.

## Discussion

When you run a summary query, normally it produces entries only for the values that are actually present in the data. Let’s say you want to produce a time-of-day summary for the records in the `mail` table, which looks like this:

```mysql> `SELECT * FROM mail;`
+---------------------+---------+---------+---------+---------+---------+
| t                   | srcuser | srchost | dstuser | dsthost | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2001-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2001-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2001-05-12 15:02:49 | phil    | mars    | phil    | saturn  |    1048 |
| 2001-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |     271 |
| 2001-05-14 09:31:37 | gene    | venus   | barb    | mars    |    2291 |
| 2001-05-14 11:52:17 | phil    | mars    | tricia  | saturn  |    5781 |
...```

To determine how many messages were sent for each hour of the day, use the following query:

```mysql> `SELECT HOUR(t) AS hour, COUNT(HOUR(t)) AS count`
-> `FROM mail GROUP BY hour;` +------+-------+ ...```

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required