We can see from the results above that Kevin Yank has three jokes to his name, and Joan Smith has one. What these results do not show is that there is a third author, Amy Mathieson, who doesn't have any jokes to her name. Since there are no entries in the Jokes table with AIDs that match her author ID, there will be no results that satisfy the WHERE clause in the query above, and she will therefore be excluded from the table of results.
About the only practical way to overcome this challenge with the tools we've seen so far would be to add another column to the Authors table and simply store the number of jokes attributed to each author in that column. Keeping that column up to date, however, would be a real pain, because we'd have to remember to update it every time a joke was added to, removed from, or changed (if, for example, the value of AID was changed) in the Jokes table. To keep things synchronized, we'd have to use LOCK TABLES whenever we made such changes, as well. Quite a mess, to say the least!
MySQL provides another method for joining tables, which fetches information from multiple tables at once. Called a left join, it's designed for just this type of situation. To understand how left joins differ from standard joins, we must first recall how standard joins work.
Standard joins take all possible combinations of rows
As shown in "Standard joins take all possible combinations of rows", MySQL performs a standard join of two tables by listing all possible combinations of the rows of those tables. In a simple case, a standard join of two tables with two rows apiece will contain four rows: row 1 of table 1 with row 1 of table 2, row 1 of table 1 with row 2 of table 2, row 2 of table 1 with row 1 of table 2, and row 2 of table 1 with row 2 of table 2. With all of these result rows calculated, MySQL then looks to the WHERE clause for guidance on which rows should actually be kept (e.g. those where the AID column from table 1 matches the ID column from table 2).
The reason the above does not suit our purposes is that we'd like to also include rows in table 1 (i.e. Authors) that don't match any rows in table 2 (i.e. Jokes). A left join does exactly what we need—it forces a row to appear in the results for each row in the first (left-hand) table, even if no matching entries are found in the second (right-hand) table. Such forced rows are given NULL values for all of the columns in the right-hand table.
To perform a left join between two tables in MySQL, separate the two table names in the FROM clause with LEFT JOIN instead of a comma. Then follow the second table's name with ON condition, where condition specifies the criteria for matching rows in the two tables (i.e. what you would normally put in the WHERE clause). Here's our revised query for listing authors and the number of jokes to their credit:
mysql>SELECT Authors.Name, COUNT(*) AS NumJokes
->FROM Authors LEFT JOIN Jokes ON AID = Authors.ID
->GROUP BY AID;
+---------------+----------+
| Name | NumJokes |
+---------------+----------+
| Amy Mathieson | 1 |
| Kevin Yank | 3 |
| Joan Smith | 1 |
+---------------+----------+
Wait just a minute! Suddenly Amy Mathieson has one joke? That can't be right! In fact, it is—but only because the query is wrong. COUNT(*) counts the number of rows returned for each author. If we look at the ungrouped results of the LEFT JOIN, we can see what's happened:
mysql>SELECT Authors.Name, Jokes.ID AS JokeID
->FROM Authors LEFT JOIN Jokes ON AID = Authors.ID;
+---------------+--------+
| Name | JokeID |
+---------------+--------+
| Kevin Yank | 1 |
| Kevin Yank | 2 |
| Kevin Yank | 4 |
| Joan Smith | 3 |
| Amy Mathieson | NULL |
+---------------+--------+
See? Amy Mathieson does have a row—the row is forced because it doesn't have any matching rows in the right-hand table of the LEFT JOIN (Jokes). The fact that the Joke ID value is NULL doesn't affect COUNT(*)—it still counts it as a row. If instead of *, you specify an actual column name (say Jokes.ID) for the COUNT function to look at, it will ignore NULL values in that column, and give us the count we want:
mysql>SELECT Authors.Name, COUNT(Jokes.ID) AS NumJokes
->FROM Authors LEFT JOIN Jokes ON AID = Authors.ID
->GROUP BY AID;
+---------------+----------+
| Name | NumJokes |
+---------------+----------+
| Amy Mathieson | 0 |
| Kevin Yank | 3 |
| Joan Smith | 1 |
+---------------+----------+