likes
comments
collection
share

Mysql—inner join/left join/right join等join的用法详解

作者站长头像
站长
· 阅读数 8

关注微信公众号:CodingTechWork,一起学习进步。

引言

  一直以来对join的几种用法都混淆,这次在别人的hive sql中看到join用法便研究总结了一下,方便后续查阅和使用。

sql join介绍

概念

Mysql—inner join/left join/right join等join的用法详解

交并集图

Mysql—inner join/left join/right join等join的用法详解

sql join示例

创建表

  1. 创建表tab_01
CREATE TABLE tab_01(
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32)
);
  1. 创建表tab_02
CREATE TABLE tab_02(
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32),
en_name VARCHAR(32),
age INT(1) 
);

初始化数据

  1. 初始化表tab_01
INSERT INTO tab_01 VALUES (1, "小明");
INSERT INTO tab_01 VALUES (2, "小王");
INSERT INTO tab_01 VALUES (3, "小红");
INSERT INTO tab_01 VALUES (4, "小陈");
  1. 初始化表tab_02
INSERT INTO tab_02 VALUES (1, "小明","A",9);
INSERT INTO tab_02 VALUES (2, "小王","B",10);
INSERT INTO tab_02 VALUES (3, "小红","C",11);
INSERT INTO tab_02 VALUES (4, "小青","D",10);
INSERT INTO tab_02 VALUES (5, "小元","E",7);
INSERT INTO tab_02 VALUES (6, "小云","F",10);

查询数据

 mysql> select * from tab_01;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   |
|  2 | 小王   |
|  3 | 小红   |
|  4 | 小陈   |
+----+--------+
4 rows in set (0.00 sec)

mysql> select * from tab_02;
+----+--------+---------+------+
| id | name   | en_name | age  |
+----+--------+---------+------+
|  1 | 小明   | A       |    9 |
|  2 | 小王   | B       |   10 |
|  3 | 小红   | C       |   11 |
|  4 | 小青   | D       |   10 |
|  5 | 小元   | E       |    7 |
|  6 | 小云   | F       |   10 |
+----+--------+---------+------+
6 rows in set (0.00 sec)

cross join

介绍

  笛卡尔积,表tab_01的行数乘以表tab_02的行数等于笛卡尔积结果集的大小。

用法

mysql> select * from tab_01 cross join tab_02;
+----+--------+----+--------+---------+------+
| id | name   | id | name   | en_name | age  |
+----+--------+----+--------+---------+------+
|  4 | 小陈   |  1 | 小明   | A       |    9 |
|  3 | 小红   |  1 | 小明   | A       |    9 |
|  2 | 小王   |  1 | 小明   | A       |    9 |
|  1 | 小明   |  1 | 小明   | A       |    9 |
|  4 | 小陈   |  2 | 小王   | B       |   10 |
|  3 | 小红   |  2 | 小王   | B       |   10 |
|  2 | 小王   |  2 | 小王   | B       |   10 |
|  1 | 小明   |  2 | 小王   | B       |   10 |
|  4 | 小陈   |  3 | 小红   | C       |   11 |
|  3 | 小红   |  3 | 小红   | C       |   11 |
|  2 | 小王   |  3 | 小红   | C       |   11 |
|  1 | 小明   |  3 | 小红   | C       |   11 |
|  4 | 小陈   |  4 | 小青   | D       |   10 |
|  3 | 小红   |  4 | 小青   | D       |   10 |
|  2 | 小王   |  4 | 小青   | D       |   10 |
|  1 | 小明   |  4 | 小青   | D       |   10 |
|  4 | 小陈   |  5 | 小元   | E       |    7 |
|  3 | 小红   |  5 | 小元   | E       |    7 |
|  2 | 小王   |  5 | 小元   | E       |    7 |
|  1 | 小明   |  5 | 小元   | E       |    7 |
|  4 | 小陈   |  6 | 小云   | F       |   10 |
|  3 | 小红   |  6 | 小云   | F       |   10 |
|  2 | 小王   |  6 | 小云   | F       |   10 |
|  1 | 小明   |  6 | 小云   | F       |   10 |
+----+--------+----+--------+---------+------+
24 rows in set (0.00 sec)

inner join

介绍

  查出表tab_01和表tab_02的完全匹配的部分。(交集)

用法

Mysql—inner join/left join/right join等join的用法详解

mysql> select * from tab_01 inner join tab_02 on tab_01.name = tab_02.name;;
+----+--------+----+--------+---------+------+
| id | name   | id | name   | en_name | age  |
+----+--------+----+--------+---------+------+
|  1 | 小明   |  1 | 小明   | A       |    9 |
|  2 | 小王   |  2 | 小王   | B       |   10 |
|  3 | 小红   |  3 | 小红   | C       |   11 |
+----+--------+----+--------+---------+------+
3 rows in set (0.00 sec)

full join

介绍

  全连接,匹配两个表所有数据。(并集)

用法

Mysql—inner join/left join/right join等join的用法详解

mysql> select * from tab_01 full outer join tab_02 on tab_01.name = tab_02.name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join tab_02 on tab_01.name = tab_02.name' at line 1

mysql中使用full join on报错,不支持,需要使用union all替换

mysql> select * from tab_01 left join tab_02 on tab_01.name = tab_02.name 
   -> union all 
   -> select * from tab_01 right join tab_02 on tab_01.name = tab_02.name;
+------+--------+------+--------+---------+------+
| id   | name   | id   | name   | en_name | age  |
+------+--------+------+--------+---------+------+
|    1 | 小明   |    1 | 小明   | A       |    9 |
|    2 | 小王   |    2 | 小王   | B       |   10 |
|    3 | 小红   |    3 | 小红   | C       |   11 |
|    4 | 小陈   | NULL | NULL   | NULL    | NULL |
|    1 | 小明   |    1 | 小明   | A       |    9 |
|    2 | 小王   |    2 | 小王   | B       |   10 |
|    3 | 小红   |    3 | 小红   | C       |   11 |
| NULL | NULL   |    4 | 小青   | D       |   10 |
| NULL | NULL   |    5 | 小元   | E       |    7 |
| NULL | NULL   |    6 | 小云   | F       |   10 |
+------+--------+------+--------+---------+------+
10 rows in set (0.00 sec)

left join

介绍

  左连接,将表tab_01的所有数据以及表tab_02所匹配到的数据进行连接输出。

用法

  1. 取出左表A中的所有数据。 Mysql—inner join/left join/right join等join的用法详解
mysql> select * from tab_01 left join tab_02 on tab_01.name = tab_02.name;
+----+--------+------+--------+---------+------+
| id | name   | id   | name   | en_name | age  |
+----+--------+------+--------+---------+------+
|  1 | 小明   |    1 | 小明   | A       |    9 |
|  2 | 小王   |    2 | 小王   | B       |   10 |
|  3 | 小红   |    3 | 小红   | C       |   11 |
|  4 | 小陈   | NULL | NULL   | NULL    | NULL |
+----+--------+------+--------+---------+------+
4 rows in set (0.00 sec)
  1. 取出左表A中不包含右表B的数据。 Mysql—inner join/left join/right join等join的用法详解
mysql> select * from tab_01 left join tab_02 on tab_01.name = tab_02.name where tab_02.name is null;
+----+--------+------+------+---------+------+
| id | name   | id   | name | en_name | age  |
+----+--------+------+------+---------+------+
|  4 | 小陈   | NULL | NULL | NULL    | NULL |
+----+--------+------+------+---------+------+
1 row in set (0.00 sec)

right join

介绍

  右连接,将表tab_02的所有数据以及表tab_01中匹配到的连接数据输出。

用法

  1. 取出所有右表B中的数据。 Mysql—inner join/left join/right join等join的用法详解
mysql> select * from tab_01 right join tab_02 on tab_01.name = tab_02.name;
+------+--------+----+--------+---------+------+
| id   | name   | id | name   | en_name | age  |
+------+--------+----+--------+---------+------+
|    1 | 小明   |  1 | 小明   | A       |    9 |
|    2 | 小王   |  2 | 小王   | B       |   10 |
|    3 | 小红   |  3 | 小红   | C       |   11 |
| NULL | NULL   |  4 | 小青   | D       |   10 |
| NULL | NULL   |  5 | 小元   | E       |    7 |
| NULL | NULL   |  6 | 小云   | F       |   10 |
+------+--------+----+--------+---------+------+
6 rows in set (0.00 sec)
  1. 取出右表B中不包含左表A的数据。 Mysql—inner join/left join/right join等join的用法详解
mysql> select * from tab_01 right join tab_02 on tab_01.name = tab_02.name where tab_01.name is null;
+------+------+----+--------+---------+------+
| id   | name | id | name   | en_name | age  |
+------+------+----+--------+---------+------+
| NULL | NULL |  4 | 小青   | D       |   10 |
| NULL | NULL |  5 | 小元   | E       |    7 |
| NULL | NULL |  6 | 小云   | F       |   10 |
+------+------+----+--------+---------+------+
3 rows in set (0.00 sec)