Understanding SQL Joins - Finding Common Ground

(Page 5 of 10 )

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)

Next: One Step Left... >>
 

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

Comments On: Understanding SQL Joins

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