Specifications

The output of this query is
+---------+--------+------------+
| orderid | amount | date |
+---------+--------+------------+
| 2 | 49.99 | 0000-00-00 |
+---------+--------+------------+
There are a few things to notice here.
First of all, because information from two tables is needed to answer this query, we have listed
both tables.
We have also specified a type of join, possibly without knowing it. The comma between the
names of the tables is equivalent to typing INNER JOIN or CROSS JOIN. This is a type of join
sometimes also referred to as a full join, or the Cartesian product of the tables. It means, “Take
the tables listed, and make one big table. The big table should have a row for each possible
combination of rows from each of the tables listed, whether that makes sense or not.” In other
words, we get a table, which has every row from the Customers table matched up with every
row from the Orders table, regardless of whether a particular customer placed a particular
order.
That doesn’t make a lot of sense in most cases. Often what we want is to see the rows that
really do match, that is, the orders placed by a particular customer matched up with that cus-
tomer.
We achieve this by placing a join condition in the WHERE clause. This is a special type of condi-
tional statement that explains which attributes show the relationship between the two tables. In
this case, our join condition was
customers.customerid = orders.customerid
which tells MySQL to only put rows in the result table if the CustomerId from the Customers
table matches the CustomerID from the Orders table.
By adding this join condition to the query, we’ve actually converted the join to a different type,
called an equi-join.
You ’ll also notice the dot notation we’ve used to make it clear which table a particular column
comes from, that is, customers.customerid refers to the customerid column from the
Customers table, and orders.customerid refers to the customerid column from the Orders
table.
This dot notation is required if the name of a column is ambiguous, that is, if it occurs in more
than one table.
Working with Your MySQL Database
C
HAPTER 9
9
W
ORKING WITH
YOUR MYSQL
DATABASE
215
12 7842 CH09 3/6/01 3:36 PM Page 215