Finding Common Ground - MySQL

Since a cross join produces a huge resultset, it is considered a good idea to attach a WHERE clause to the join to filter out some of the records. Here's an example:

SELECT * FROM a,b WHERE a1 > 30;

This returns

+----+------+----+------+
| a1 | a2   | b1 | b2   |
+----+------+----+------+
| 40 | x    | 10 | p    |
| 50 | y    | 10 | p    |
| 60 | z    | 10 | p    |
| 40 | x    | 20 | q    |
| 50 | y    | 20 | q    |
| 60 | z    | 20 | q    |
+----+------+----+------+
6 rows in set (0.06 sec)

A variant of the cross join is the "equi-join", where certain fields in the joined tables are equated to each other. In this case, the final resultset will only include those rows from the joined tables which have matches in the specified fields.

SELECT * FROM a,b WHERE a1 = b1;

This returns

+----+------+----+------+
| a1 | a2   | b1 | b2   |
+----+------+----+------+
| 10 | u    | 10 | p    |
| 20 | v    | 20 | q    |
+----+------+----+------+
2 rows in set (0.00 sec)

Here, too, you can use a WHERE clause to further narrow the resultset:

SELECT * FROM a,b WHERE a1 = b1 AND a1 = 20;

This returns

+----+------+----+------+
| a1 | a2   | b1 | b2   |
+----+------+----+------+
| 20 | v    | 20 | q    |
+----+------+----+------+
1 row in set (0.00 sec)

>>> More MySQL Articles          >>> More By The Disenchanted Developer, (c) Melonfire

blog comments powered by Disqus