Specifications

have values in common. If we want to find customers who live in the same city—perhaps to
set up a reading group—we can give the same table (Customers) two different aliases:
select c1.name, c2.name, c1.city
from customers as c1, customers as c2
where c1.city = c2.city
and c1.name != c2.name;
What we are basically doing is pretending that the table Customers is two different tables, c1
and c2, and performing a join on the City column. You will notice that we also need the sec-
ond condition, c1.name != c2.name—this is to avoid each customer coming up as a match to
herself.
Summary of Joins
The different types of joins we have looked at are summarized in Table 9.2. There are a few
others, but these are the main ones you will use.
T
ABLE 9.2 Join Types in MySQL
Name Description
Cartesian product All combinations of all the rows in all the tables in the join. Used by
specifying a comma between table names, and not specifying a WHERE
clause.
Full join Same as preceding.
Cross join Same as preceding. Can also be used by specifying the CROSS JOIN
keywords between the names of the tables being joined.
Inner join Semantically equivalent to the comma. Can also be specified using
the INNER JOIN keywords. Without a WHERE condition, equivalent to a
full join. Usually, you will specify a WHERE condition to make this a
true inner join.
Equi-join Uses a conditional expression with an
= to match rows from the dif-
ferent tables in the join. In SQL, this is a join with a
WHERE clause.
Left join Tries to match rows across tables and fills in nonmatching rows with
NULLs. Use in SQL with the LEFT JOIN keywords. Used for finding
missing values. You can equivalently use RIGHT JOIN.
Retrieving Data in a Particular Order
If you want to display rows retrieved by a query in a particular order, you can use the ORDER
BY clause of the SELECT statement. This feature is handy for presenting output in a good
human-readable format.
Working with Your MySQL Database
C
HAPTER 9
9
W
ORKING WITH
YOUR MYSQL
DATABASE
219
12 7842 CH09 3/6/01 3:36 PM Page 219