Introduction to Joins

In the absence of a more tutorial-level document, here is a simple example of three basic JOIN types, which you can experiment with in order to see what the different joins accomplish:

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (2), (4);
SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;
SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b;
SELECT * FROM t2 LEFT JOIN t1 ON t1.a = t2.b;

The first two SELECTs are (unfortunately) commonly written with an older form:

SELECT * FROM t1, t2 WHERE t1.a = t2.b;
SELECT * FROM t1, t2;

What you can see from this is that an INNER JOIN produces a result set containing only rows that have a match, in both tables (t1 and t2), for the specified join condition(s).

A CROSS JOIN produces a result set in which every row in each table is joined to every row in the other table; this is also called a cartesian product.

The LEFT JOIN is an outer join, which produces a result set with all rows from the table on the "left" (t1); the values for the columns in the other table (t2) depend on whether or not a match was found. If no match is found, all columns from that table are set to NULL for that row.

Here is the output of the various SELECT statements listed above:

SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;
 ------ ------ 
| a    | b    |
 ------ ------ 
|    2 |    2 |
 ------ ------ 
1 row in set (0.00 sec)

SELECT * FROM t1 CROSS JOIN t2;
 ------ ------ 
| a    | b    |
 ------ ------ 
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
 ------ ------ 
6 rows in set (0.00 sec)

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b;
 ------ ------ 
| a    | b    |
 ------ ------ 
|    1 | NULL |
|    2 |    2 |
|    3 | NULL |
 ------ ------ 
3 rows in set (0.00 sec)

SELECT * FROM t2 LEFT JOIN t1 ON t1.a = t2.b;
 ------ ------ 
| b    | a    |
 ------ ------ 
|    2 |    2 |
|    4 | NULL |
 ------ ------ 
2 rows in set (0.00 sec)

That should give you a bit more understanding of how JOINS work!

Other References

[1] - Nice tutorial. Part 5 covers joins.

See Also

Comma vs JOIN