Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources] نسخه متنی

LEFT JOINs

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