Specifications

This output shows us that there are no matching orderids for customers Melissa Jones and
Michael Archer because the orderids for those customers are NULLs.
If we want to see only the customers who haven’t ordered anything, we can do this by check-
ing for those NULLs in the primary key field of the right table (in this case orderid) as that
should not be NULL in any real rows:
select customers.customerid, customers.name
from customers left join orders
using (customerid)
where orders.orderid is null;
The result is
+------------+----------------+
| customerid | name |
+------------+----------------+
| 4 | Melissa Jones |
| 5 | Michael Archer |
+------------+----------------+
You ’ll also notice that we used a different syntax for the join condition in this example. Left
joins support either the ON syntax we used in the first example, or the USING syntax in the sec-
ond example. Notice that the USING syntax doesn’t specify the table from which the join
attribute comes—for this reason, the columns in the two tables must have the same name if
you want to use USING.
Using Other Names for Tables: Aliases
It is often handy and occasionally essential to be able to refer to tables by other names. Other
names for tables are called aliases. You can create these at the start of a query and then use
them throughout. They are often handy as shorthand. Consider the huge query we looked at
earlier, rewritten with aliases:
select c.name
from customers as c, orders as o, order_items as oi, books as b
where c.customerid = o.customerid
and o.orderid = oi.orderid
and oi.isbn = b.isbn
and b.title like ‘%Java%’;
As we declare the tables we are going to use, we add an AS clause to declare the alias for that
table. We can also use aliases for columns, but we’ll return to this when we look at aggregate
functions in a minute.
We need to use table aliases when we want to join a table to itself. This sounds more difficult
and esoteric than it is. It is useful, if, for example, we want to find rows in the same table that
Using MySQL
P
ART II
218
12 7842 CH09 3/6/01 3:36 PM Page 218