1.表连接，就是将两个表合并起来，被合并的表的记录要通过中间字段，一一匹配起来左边的表的记录，形成一张临时的合并的表，并且每条记录的值都是两张表一一准确对应的

实例

尝试以下实例：

root@host# mysql -u root -p password;Enter password:*******mysql> use RUNOOB;Database changedmysql> SELECT * FROM tcount_tbl;+-----------------+----------------+| runoob_author | runoob_count |+-----------------+----------------+| mahran          |             20 || mahnaz          |           NULL || Jen             |           NULL || Gill            |             20 || John Poul       |              1 || Sanjay          |              1 |+-----------------+----------------+6 rows in set (0.01 sec)mysql> SELECT * from runoob_tbl;+-------------+----------------+-----------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-------------+----------------+-----------------+-----------------+|           1 | Learn PHP      | John Poul       | 2007-05-24      ||           2 | Learn MySQL    | Abdul S         | 2007-05-24      ||           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |+-------------+----------------+-----------------+-----------------+3 rows in set (0.00 sec)mysql>

接下来我们就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;+-----------+---------------+--------------+| runoob_id | runoob_author | runoob_count |+-----------+---------------+--------------+|         1 | John Poul     |            1 ||         3 | Sanjay        |            1 |+-----------+---------------+--------------+2 rows in set (0.00 sec)

以上 SQL 语句等价于：

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;+-------------+-----------------+----------------+| runoob_id | runoob_author | runoob_count |+-------------+-----------------+----------------+|           1 | John Poul       |              1 ||           3 | Sanjay          |              1 |+-------------+-----------------+----------------+2 rows in set (0.01 sec)mysql>

MySQL LEFT JOIN

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

实例

尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解MySQL LEFT JOIN的应用：

root@host# mysql -u root -p password;Enter password:*******mysql> use RUNOOB;Database changedmysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;+-------------+-----------------+----------------+| runoob_id | runoob_author | runoob_count |+-------------+-----------------+----------------+|           1 | John Poul       |              1 ||           2 | Abdul S         |           NULL ||           3 | Sanjay          |              1 |+-------------+-----------------+----------------+3 rows in set (0.02 sec)

mysql join表连接