MySQL连接join的使用方法

马谦马谦马谦

615
文章

17
评论

2017年9月26日21:53:51MySQL评论317字数 605阅读2分1秒阅读模式

一、概述

join 可以用来描述多个表之间的关系，主要有以下几种用法：

left join：左连接
right join：右连接
inner join：内连接
cross join：交叉连接，笛卡尔积。

语法形式为：

[select | update] * from table1 [inner | left | right | cross] join table2 on *

1	[select \| update] * from table1 [inner \| left \| right \| cross] join table2 on *

二、建表

首先准备两张数据表：join_test_ajoin_test_b

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`)
);

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`)

);

分别插入数据：

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");

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");

数据状态：

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

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

;

+----+------+------+-------------+

+----+------+------+-------------+

| 1 | 小花 | 湖南 | 13911223344 |

| 2 | 小明 | 广东 | 13789897676 |

| 3 | 小贱 | 广东 | 18766554433 |

| 4 | 小白 | 北京 | 13876543210 |

+----+------+------+-------------+

4 rows in set

三、Left join

left join 用来取出左表中所有的记录以及右表中的具有对应关系的记录：

# 取出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

# 取出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表有对应关系的记录：

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

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：

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

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的关系

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 |
+----+------+-----+----+------+------+-------------+