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.

Next: The Equi-join >>
 

More MySQL Articles
More By W.J. Gilmore

Comments On: MySQL Table Joins

>>> Be the FIRST to comment on this article!