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

技术公众号：后端技术解忧铺
关注微信公众号：CodingTechWork，一起学习进步。

引言

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

sql join介绍

概念

join思维导图

交并集图

sql joins

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

介绍

  全连接，匹配两个表所有数据。（并集）

用法

full 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中的所有数据。
    left 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的数据。
    left join B is null
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中的数据。
    right 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的数据。
    righ join A is null
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)
posted @   Andya_net  阅读(103)  评论(0)  编辑  收藏  举报  
相关博文：
· HIVE——常用sql命令总结
· MySQL—sql语句多个字段模糊查询并指定优先级排序
· mysql中的left join、right join 、inner join的详细用法
· SQL 的各种 JOIN 用法
· sql语法：inner join on, left join on, right join on详细使用方法
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛