一、概述
join 可以用来描述多个表之间的关系，主要有以下几种用法：
left join：左连接right join：右连接inner join：内连接cross join：交叉连接，笛卡尔积。
语法形式为：
|
1 |
[select | update] * from table1 [inner | left | right | cross] join table2 on * |
二、建表
首先准备两张数据表：join_test_ajoin_test_b
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE `join_test_a` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `join_test_b` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NULL, `addr` varchar(255) NULL, `tele` varchar(11) NULL, PRIMARY KEY (`id`) ); |
分别插入数据：
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO `test`.`join_test_a` (`id`, `name`, `age`) VALUES ("1", "小明", "16"); INSERT INTO `test`.`join_test_a` (`id`, `name`, `age`) VALUES ("2", "小花", "17"); INSERT INTO `test`.`join_test_a` (`id`, `name`, `age`) VALUES ("3", "小白", "18"); INSERT INTO `test`.`join_test_a` (`id`, `name`, `age`) VALUES ("4", "小黑", "19"); INSERT INTO `test`.`join_test_a` (`id`, `name`, `age`) VALUES ("5", "老王", "40"); INSERT INTO `test`.`join_test_b` (`id`, `name`, `addr`, `tele`) VALUES ("1", "小花", "湖南", "13911223344"); INSERT INTO `test`.`join_test_b` (`id`, `name`, `addr`, `tele`) VALUES ("2", "小明", "广东", "13789897676"); INSERT INTO `test`.`join_test_b` (`id`, `name`, `addr`, `tele`) VALUES ("3", "小贱", "广东", "18766554433"); INSERT INTO `test`.`join_test_b` (`id`, `name`, `addr`, `tele`) VALUES ("4", "小白", "北京", "13876543210"); |
数据状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> select * from join_test_a; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | 小明 | 16 | | 2 | 小花 | 17 | | 3 | 小白 | 18 | | 4 | 小黑 | 19 | | 5 | 老王 | 40 | +----+------+-----+ 5 rows in set mysql> select * from join_test_b ; +----+------+------+-------------+ | id | name | addr | tele | +----+------+------+-------------+ | 1 | 小花 | 湖南 | 13911223344 | | 2 | 小明 | 广东 | 13789897676 | | 3 | 小贱 | 广东 | 18766554433 | | 4 | 小白 | 北京 | 13876543210 | +----+------+------+-------------+ 4 rows in set |
三、 Left join
left join 用来取出左表中所有的记录以及右表中的具有对应关系的记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# 取出 a 表中的所有记录以及 b 表中名字和 a 表名字相等的记录 mysql> select * from join_test_a left join join_test_b on join_test_a.name = join_test_b.name; +----+------+-----+------+------+------+-------------+ | id | name | age | id | name | addr | tele | +----+------+-----+------+------+------+-------------+ | 2 | 小花 | 17 | 1 | 小花 | 湖南 | 13911223344 | | 1 | 小明 | 16 | 2 | 小明 | 广东 | 13789897676 | | 3 | 小白 | 18 | 4 | 小白 | 北京 | 13876543210 | | 4 | 小黑 | 19 | NULL | NULL | NULL | NULL | | 5 | 老王 | 40 | NULL | NULL | NULL | NULL | +----+------+-----+------+------+------+-------------+ 5 rows in set |
四、 Right Join
right join 和 left join 相反，right join 用来取出 b 表中所有的记录以及 a 表有对应关系的记录：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select * from join_test_a right join join_test_b on join_test_a.name = join_test_b.name; +------+------+------+----+------+------+-------------+ | id | name | age | id | name | addr | tele | +------+------+------+----+------+------+-------------+ | 1 | 小明 | 16 | 2 | 小明 | 广东 | 13789897676 | | 2 | 小花 | 17 | 1 | 小花 | 湖南 | 13911223344 | | 3 | 小白 | 18 | 4 | 小白 | 北京 | 13876543210 | | NULL | NULL | NULL | 3 | 小贱 | 广东 | 18766554433 | +------+------+------+----+------+------+-------------+ 4 rows in set |
五、 Inner Join
inner join 用来查询表之间都互相满足依赖关系的记录，即 A∩B：
|
1 2 3 4 5 6 7 8 9 |
mysql> select * from join_test_a inner join join_test_b on join_test_a.name = join_test_b.name; +----+------+-----+----+------+------+-------------+ | id | name | age | id | name | addr | tele | +----+------+-----+----+------+------+-------------+ | 1 | 小明 | 16 | 2 | 小明 | 广东 | 13789897676 | | 2 | 小花 | 17 | 1 | 小花 | 湖南 | 13911223344 | | 3 | 小白 | 18 | 4 | 小白 | 北京 | 13876543210 | +----+------+-----+----+------+------+-------------+ 3 rows in set |
六、 Cross Join
cross join 用来查询表间的笛卡尔积，即 a*b 的关系
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> select * from join_test_a cross join join_test_b; +----+------+-----+----+------+------+-------------+ | id | name | age | id | name | addr | tele | +----+------+-----+----+------+------+-------------+ | 1 | 小明 | 16 | 1 | 小花 | 湖南 | 13911223344 | | 1 | 小明 | 16 | 2 | 小明 | 广东 | 13789897676 | | 1 | 小明 | 16 | 3 | 小贱 | 广东 | 18766554433 | | 1 | 小明 | 16 | 4 | 小白 | 北京 | 13876543210 | | 2 | 小花 | 17 | 1 | 小花 | 湖南 | 13911223344 | | 2 | 小花 | 17 | 2 | 小明 | 广东 | 13789897676 | | 2 | 小花 | 17 | 3 | 小贱 | 广东 | 18766554433 | | 2 | 小花 | 17 | 4 | 小白 | 北京 | 13876543210 | | 3 | 小白 | 18 | 1 | 小花 | 湖南 | 13911223344 | | 3 | 小白 | 18 | 2 | 小明 | 广东 | 13789897676 | | 3 | 小白 | 18 | 3 | 小贱 | 广东 | 18766554433 | | 3 | 小白 | 18 | 4 | 小白 | 北京 | 13876543210 | | 4 | 小黑 | 19 | 1 | 小花 | 湖南 | 13911223344 | | 4 | 小黑 | 19 | 2 | 小明 | 广东 | 13789897676 | | 4 | 小黑 | 19 | 3 | 小贱 | 广东 | 18766554433 | | 4 | 小黑 | 19 | 4 | 小白 | 北京 | 13876543210 | | 5 | 老王 | 40 | 1 | 小花 | 湖南 | 13911223344 | | 5 | 老王 | 40 | 2 | 小明 | 广东 | 13789897676 | | 5 | 老王 | 40 | 3 | 小贱 | 广东 | 18766554433 | | 5 | 老王 | 40 | 4 | 小白 | 北京 | 13876543210 | +----+------+-----+----+------+------+-------------+ |