If you specify two tables in your SELECT, by default you'll get
a result set which includes each record of the first table combined
with each record of the second table.  This can make for a huge
result set - for example, if you have tables that you "join" in this
way with 6 and 10 rows, the result set will be 6 * 10 = 60 rows.

Code:

mysql> select * from leave, arrive; +----------+------------+---------+-------+----------+-------+ | departs  | away       | codenum | refno | arrives  | refno | +----------+------------+---------+-------+----------+-------+ | 06:35:00 | Swindon    |       1 |     1 | 07:01:00 |     1 | | 06:51:00 | Chippenham |       1 |     2 | 07:01:00 |     1 | | 08:46:00 | Swindon    |       2 |     3 | 07:01:00 |     1 | | 09:02:00 | Chippenham |       2 |     4 | 07:01:00 |     1 | | 14:23:00 | Swindon    |       3 |     5 | 07:01:00 |     1 | | 14:39:00 | Chippenham |       3 |     6 | 07:01:00 |     1 | | 17:53:00 | Swindon    |       4 |     7 | 07:01:00 |     1 | | 18:08:00 | Chippenham |       4 |     8 | 07:01:00 |     1 | | 22:01:00 | Swindon    |       5 |     9 | 07:01:00 |     1 | | 22:17:00 | Chippenham |       5 |    10 | 07:01:00 |     1 | | 06:35:00 | Swindon    |       1 |     1 | 09:11:00 |     2 | | 06:51:00 | Chippenham |       1 |     2 | 09:11:00 |     2 | | 08:46:00 | Swindon    |       2 |     3 | 09:11:00 |     2 | | 09:02:00 | Chippenham |       2 |     4 | 09:11:00 |     2 | | 14:23:00 | Swindon    |       3 |     5 | 09:11:00 |     2 | | 14:39:00 | Chippenham |       3 |     6 | 09:11:00 |     2 | | 17:53:00 | Swindon    |       4 |     7 | 09:11:00 |     2 | | 18:08:00 | Chippenham |       4 |     8 | 09:11:00 |     2 | | 22:01:00 | Swindon    |       5 |     9 | 09:11:00 |     2 | | 22:17:00 | Chippenham |       5 |    10 | 09:11:00 |     2 | | 06:35:00 | Swindon    |       1 |     1 | 18:18:00 |     4 | | 06:51:00 | Chippenham |       1 |     2 | 18:18:00 |     4 | | 08:46:00 | Swindon    |       2 |     3 | 18:18:00 |     4 | | 09:02:00 | Chippenham |       2 |     4 | 18:18:00 |     4 | | 14:23:00 | Swindon    |       3 |     5 | 18:18:00 |     4 | | 14:39:00 | Chippenham |       3 |     6 | 18:18:00 |     4 | | 17:53:00 | Swindon    |       4 |     7 | 18:18:00 |     4 | | 18:08:00 | Chippenham |       4 |     8 | 18:18:00 |     4 | | 22:01:00 | Swindon    |       5 |     9 | 18:18:00 |     4 | | 22:17:00 | Chippenham |       5 |    10 | 18:18:00 |     4 | | 06:35:00 | Swindon    |       1 |     1 | 22:27:00 |     5 | | 06:51:00 | Chippenham |       1 |     2 | 22:27:00 |     5 | | 08:46:00 | Swindon    |       2 |     3 | 22:27:00 |     5 | | 09:02:00 | Chippenham |       2 |     4 | 22:27:00 |     5 | | 14:23:00 | Swindon    |       3 |     5 | 22:27:00 |     5 | | 14:39:00 | Chippenham |       3 |     6 | 22:27:00 |     5 | | 17:53:00 | Swindon    |       4 |     7 | 22:27:00 |     5 | | 18:08:00 | Chippenham |       4 |     8 | 22:27:00 |     5 | | 22:01:00 | Swindon    |       5 |     9 | 22:27:00 |     5 | | 22:17:00 | Chippenham |       5 |    10 | 22:27:00 |     5 | | 06:35:00 | Swindon    |       1 |     1 | 14:49:00 |     6 | | 06:51:00 | Chippenham |       1 |     2 | 14:49:00 |     6 | | 08:46:00 | Swindon    |       2 |     3 | 14:49:00 |     6 | | 09:02:00 | Chippenham |       2 |     4 | 14:49:00 |     6 | | 14:23:00 | Swindon    |       3 |     5 | 14:49:00 |     6 | | 14:39:00 | Chippenham |       3 |     6 | 14:49:00 |     6 | | 17:53:00 | Swindon    |       4 |     7 | 14:49:00 |     6 | | 18:08:00 | Chippenham |       4 |     8 | 14:49:00 |     6 | | 22:01:00 | Swindon    |       5 |     9 | 14:49:00 |     6 | | 22:17:00 | Chippenham |       5 |    10 | 14:49:00 |     6 | | 06:35:00 | Swindon    |       1 |     1 | 09:21:00 |     7 | | 06:51:00 | Chippenham |       1 |     2 | 09:21:00 |     7 | | 08:46:00 | Swindon    |       2 |     3 | 09:21:00 |     7 | | 09:02:00 | Chippenham |       2 |     4 | 09:21:00 |     7 | | 14:23:00 | Swindon    |       3 |     5 | 09:21:00 |     7 | | 14:39:00 | Chippenham |       3 |     6 | 09:21:00 |     7 | | 17:53:00 | Swindon    |       4 |     7 | 09:21:00 |     7 | | 18:08:00 | Chippenham |       4 |     8 | 09:21:00 |     7 | | 22:01:00 | Swindon    |       5 |     9 | 09:21:00 |     7 | | 22:17:00 | Chippenham |       5 |    10 | 09:21:00 |     7 | +----------+------------+---------+-------+----------+-------+ 60 rows in set (0.01 sec)

Clearly, this is not usually what you require, so you'll use a where
clause to select only those records in the result set above where
there's a matching key of come sort:

Code:

mysql> select * from arrive, leave where leave.codenum = arrive.refno; +----------+-------+----------+------------+---------+-------+ | arrives  | refno | departs  | away       | codenum | refno | +----------+-------+----------+------------+---------+-------+ | 07:01:00 |     1 | 06:35:00 | Swindon    |       1 |     1 | | 07:01:00 |     1 | 06:51:00 | Chippenham |       1 |     2 | | 09:11:00 |     2 | 08:46:00 | Swindon    |       2 |     3 | | 09:11:00 |     2 | 09:02:00 | Chippenham |       2 |     4 | | 18:18:00 |     4 | 17:53:00 | Swindon    |       4 |     7 | | 18:18:00 |     4 | 18:08:00 | Chippenham |       4 |     8 | | 22:27:00 |     5 | 22:01:00 | Swindon    |       5 |     9 | | 22:27:00 |     5 | 22:17:00 | Chippenham |       5 |    10 | +----------+-------+----------+------------+---------+-------+ 8 rows in set (0.00 sec)

* A Left Join

The join that we've just looked at correctly identified the 8
records where the keys matched.   Sometimes, we'll want a result
set that includes at least one record for every record in one of the
incoming tables (let's say the left table) even if there's no corresponding
entry in the right table.  If we use a LEFT JOIN, we can do this:

Code:

mysql> select * from arrive left join leave on leave.codenum = arrive.refno; +----------+-------+----------+------------+---------+-------+ | arrives  | refno | departs  | away       | codenum | refno | +----------+-------+----------+------------+---------+-------+ | 07:01:00 |     1 | 06:35:00 | Swindon    |       1 |     1 | | 07:01:00 |     1 | 06:51:00 | Chippenham |       1 |     2 | | 09:11:00 |     2 | 08:46:00 | Swindon    |       2 |     3 | | 09:11:00 |     2 | 09:02:00 | Chippenham |       2 |     4 | | 18:18:00 |     4 | 17:53:00 | Swindon    |       4 |     7 | | 18:18:00 |     4 | 18:08:00 | Chippenham |       4 |     8 | | 22:27:00 |     5 | 22:01:00 | Swindon    |       5 |     9 | | 22:27:00 |     5 | 22:17:00 | Chippenham |       5 |    10 | | 14:49:00 |     6 | NULL     | NULL       |    NULL |  NULL | | 09:21:00 |     7 | NULL     | NULL       |    NULL |  NULL | +----------+-------+----------+------------+---------+-------+ 10 rows in set (0.00 sec)

The additional records that left join creates are "completed"
with nulls, so that you can use a chack for null on a NOT NULL
field to identify them - great if you're looking for holes in
your structure:

Code:

mysql> select * from arrive left join leave on leave.codenum = arrive.refno    -> where leave.codenum is NULL; +----------+-------+---------+------+---------+-------+ | arrives  | refno | departs | away | codenum | refno | +----------+-------+---------+------+---------+-------+ | 14:49:00 |     6 | NULL    | NULL |    NULL |  NULL | | 09:21:00 |     7 | NULL    | NULL |    NULL |  NULL | +----------+-------+---------+------+---------+-------+ 2 rows in set (0.00 sec)

In more recent versions of MySQL, there's a right join too.

---