Specifications

This query will return the following output:
+-----------------+
| name |
+-----------------+
| Michelle Arthur |
+-----------------+
Notice that we traced the data through four different tables, and to do this with an equi-join,
we needed three different join conditions. It is generally true that you need one join condition
for each pair of tables that you want to join, and therefore a total of join conditions one less
than the total number of tables you want to join. This rule of thumb can be useful for debug-
ging queries that don’t quite work. Check off your join conditions and make sure you’ve fol-
lowed the path all the way from what you know to what you want to know.
Finding Rows That Don’t Match
The other main type of join that you will use in MySQL is the left join.
In the previous examples, you’ll notice that only the rows where there was a match between the
tables were included. Sometimes we specifically want the rows where there’s no match—for
example, customers who have never placed an order, or books that have never been ordered.
The easiest way to answer this type of question in MySQL is to use a left join. A left join will
match up rows on a specified join condition between two tables. If there’s no matching row in
the right table, a row will be added to the result that contains NULL values in the right columns.
Let’s look at an example:
select customers.customerid, customers.name, orders.orderid
from customers left join orders
on customers.customerid = orders.customerid;
This SQL query uses a left join to join Customers with Orders. You will notice that the left join
uses a slightly different syntax for the join condition—in this case, the join condition goes in a
special ON clause of the SQL statement.
The result of this query is
+------------+-----------------+---------+
| customerid | name | orderid |
+------------+-----------------+---------+
| 1 | Julie Smith | 2 |
| 2 | Alan Wong | 3 |
| 3 | Michelle Arthur | 1 |
| 3 | Michelle Arthur | 4 |
| 4 | Melissa Jones | NULL |
| 5 | Michael Archer | NULL |
+------------+-----------------+---------+
Working with Your MySQL Database
C
HAPTER 9
9
W
ORKING WITH
YOUR MYSQL
DATABASE
217
12 7842 CH09 3/6/01 3:36 PM Page 217