MySQL Table Joins - The Cross Join (
Page 2 of 5 )
The
cross-join is a very basic type of join that simply matches each row from
one table to every row from another table. While not very efficient, it does
illustrate the common characteristic of all joins: table unification.
An example of a cross-join would be as follows:
mysql> SELECT * FROM pcs, clients;
So why are there so many rows? Remember that a cross-join matches up each row
of one table with every row of the second table. Therefore, a join involving two
tables in which one table has 3 rows and the second table has 4 rows would
result in 12 rows.
Perhaps it is easy to remember the cross-join by putting the result in the
perspective that each row in table1 crosses every row in table 2 once.
Voilą! You have successfully performed your first join! Not so hard after
all, is it?
Now try the following and see what happens:
mysql> select c.name, o.cid from orders o, clients c where o.cid = "acm-042";
| name |
cid |
| acme, Inc. |
acm-042 |
| acme, Inc. |
acm-042 |
| acme, Inc. |
acm-042 |
| widgets-r-us, inc. |
acm-042 |
| widgets-r-us, inc. |
acm-042 |
| widgets-r-us, inc. |
acm-042 |
| italimp, inc. |
acm-042 |
| italimp, inc. |
acm-042 |
| italimp, inc. |
acm-042 |
| fedey, inc. |
acm-042 |
| fedey, inc. |
acm-042 |
| fedey, inc. |
acm-042 |
So what did you think about the result? Was it what you expected it to be?
This time, each name in table clients was matched to every row in orders
containing "acm-042". It still fits our above explanation of a cross-join, yet
it is more selective in which rows are "crossed". Try playing around with the
order of the query, and see what happens. Notice that when the table order is
switched, the output will change accordingly.
Note: Why are aliases used when naming
tables? Aliases are used as a method of saving repetitious keystrokes when
entering queries. Thus, instead of repeatedly spelling out 'clients' when
specifying columns, we could use 'c' and then state 'from clients c' within our
query.
Although the cross-join does in fact bring our tables together, it is not
very efficient. So how can we sparingly choose the data we want from each table?
Read on to find out more.
/ 108 
