MySQL基础~使用SQL99实现7种JOIN操作（取并操作）

最新推荐文章于 2023-11-21 17:25:48 发布
最新推荐文章于 2023-11-21 17:25:48 发布
阅读量1.3k 收藏 5
点赞数 3
分类专栏： MySQL 文章标签： mysql 数据库 database Java 数据结构
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Merciful_Lion/article/details/124526098
版权

文章目录

union 与 union all关键字

UNION的作用是合并查询的结果 ，去除重复的记录
在这里插入图片描述
UNION ALL的作用是合并查询结果，不去掉重复的记录
在这里插入图片描述
语法格式

select 某列... from 表1
union /union all
select 某列....from 表2



优先使用：
union 返回两个表的内容（并集），去重

union all 
返回 两个表的内容（并集） 不去重

在优先级选择上，优先选择使用union all,因为 union 有一个去重操作，会导致运行效率比较慢 ，但这是在明确结果里没有重复数据的情况下，如果有重复的数据，还是使用union

使用union、union all的要求：
两个结果并的时候 列数要相同 列的数据类型也要相同

7种JOIN操作的实现

7中JOIN操作就是查询两张表后返回结果的几种可能，可能是内连接（A∩B），左外、右外、A-A∩B、B-A∩B、A∪B-A∩B、全部返回（A∪B）共7种结果，如下图：
在这里插入图片描述

A∩B内连接

最中间的图，即返回两张表之间的交集，就是内连接

举例：返回员工表中的员工id和部门表中对应的部分 ，连接条件是部门id相等
mysql> SELECT employee_id,department_name
    ->  FROM employees e JOIN departments d
    ->  ON e.`department_id`=d.`department_id`;
+-------------+------------------+
| employee_id | department_name  |
+-------------+------------------+
|         200 | Administration   |
|         201 | Marketing        |
|         202 | Marketing        |
|         114 | Purchasing       |
|         115 | Purchasing       |
|         116 | Purchasing       |
|         117 | Purchasing       |
|         118 | Purchasing       |
|         119 | Purchasing       |
|         203 | Human Resources  |
|         120 | Shipping         |
|         121 | Shipping         |
|         122 | Shipping         |
|         123 | Shipping         |
|         124 | Shipping         |
|         125 | Shipping         |
|         126 | Shipping         |
|         127 | Shipping         |
|         128 | Shipping         |
|         129 | Shipping         |
|         130 | Shipping         |
|         131 | Shipping         |
|         132 | Shipping         |
|         133 | Shipping         |
|         134 | Shipping         |
|         135 | Shipping         |
|         136 | Shipping         |
|         137 | Shipping         |
|         138 | Shipping         |
|         139 | Shipping         |
|         140 | Shipping         |
|         141 | Shipping         |
|         142 | Shipping         |
|         143 | Shipping         |
|         144 | Shipping         |
|         180 | Shipping         |
|         181 | Shipping         |
|         182 | Shipping         |
|         183 | Shipping         |
|         184 | Shipping         |
|         185 | Shipping         |
|         186 | Shipping         |
|         187 | Shipping         |
|         188 | Shipping         |
|         189 | Shipping         |
|         190 | Shipping         |
|         191 | Shipping         |
|         192 | Shipping         |
|         193 | Shipping         |
|         194 | Shipping         |
|         195 | Shipping         |
|         196 | Shipping         |
|         197 | Shipping         |
|         198 | Shipping         |
|         199 | Shipping         |
|         103 | IT               |
|         104 | IT               |
|         105 | IT               |
|         106 | IT               |
|         107 | IT               |
|         204 | Public Relations |
|         145 | Sales            |
|         146 | Sales            |
|         147 | Sales            |
|         148 | Sales            |
|         149 | Sales            |
|         150 | Sales            |
|         151 | Sales            |
|         152 | Sales            |
|         153 | Sales            |
|         154 | Sales            |
|         155 | Sales            |
|         156 | Sales            |
|         157 | Sales            |
|         158 | Sales            |
|         159 | Sales            |
|         160 | Sales            |
|         161 | Sales            |
|         162 | Sales            |
|         163 | Sales            |
|         164 | Sales            |
|         165 | Sales            |
|         166 | Sales            |
|         167 | Sales            |
|         168 | Sales            |
|         169 | Sales            |
|         170 | Sales            |
|         171 | Sales            |
|         172 | Sales            |
|         173 | Sales            |
|         174 | Sales            |
|         175 | Sales            |
|         176 | Sales            |
|         177 | Sales            |
|         179 | Sales            |
|         100 | Executive        |
|         101 | Executive        |
|         102 | Executive        |
|         108 | Finance          |
|         109 | Finance          |
|         110 | Finance          |
|         111 | Finance          |
|         112 | Finance          |
|         113 | Finance          |
|         205 | Accounting       |
|         206 | Accounting       |
+-------------+------------------+
106 rows in set (0.00 sec)
左外连接&右外连接

左上角的图对应典型的左外连接，右上角的图对应典型的右外连接，就是在返回两个表并集的基础上，分别加上了左表剩余部分或者右表剩余部分

举例：
可能有些员工没有部门 或者有些部门没有员工 将这两种情况一起查询出来

left join
# 左外连接： 
 SELECT employee_id,department_name
 FROM employees e LEFT JOIN departments d
 ON e.`department_id`=d.`department_id`;
 
right join
# 右外连接：
 SELECT employee_id department_name
 FROM employees e RIGHT JOIN departments d
 ON e.`department_id`=d.`department_id`;

查询的结果集比较长，这里不再展示
A - A∩B

左中图对应的情况是A - A∩B，就是返回A表中除去A、B交集的部分，可以在左外连接的基础上，加上筛选条件，去掉重复部分

举例：查询出员工没有部门的情况
mysql> SELECT employee_id,department_name
    -> FROM employees e LEFT JOIN departments d
    -> ON e.`department_id`=d.`department_id`
    -> WHERE d.`department_id` IS NULL;
+-------------+-----------------+
| employee_id | department_name |
+-------------+-----------------+
|         178 | NULL            |
+-------------+-----------------+
1 row in set (0.00 sec)
B-A∩B

右中图对应的情况是B-A∩B，与上面的情况相反，返回B表中剩余部分，即除去与A表相关联的部分

举例：查询出有些部门没有员工的情况
mysql> SELECT employee_id,department_name
    -> FROM employees e RIGHT JOIN departments d
    -> ON e.`department_id`=d.`department_id`
    -> WHERE e.`department_id` IS NULL;
+-------------+----------------------+
| employee_id | department_name      |
+-------------+----------------------+
|        NULL | Treasury             |
|        NULL | Corporate Tax        |
|        NULL | Control And Credit   |
|        NULL | Shareholder Services |
|        NULL | Benefits             |
|        NULL | Manufacturing        |
|        NULL | Construction         |
|        NULL | Contracting          |
|        NULL | Operations           |
|        NULL | IT Support           |
|        NULL | NOC                  |
|        NULL | IT Helpdesk          |
|        NULL | Government Sales     |
|        NULL | Retail Sales         |
|        NULL | Recruiting           |
|        NULL | Payroll              |
+-------------+----------------------+
16 rows in set (0.00 sec)
满外连接（A∪B）

对应左下图，可以看成是左上图+UNION ALL+右中图
我们明确知道左上图和右中图这两种返回结果没有重复的数据，所以可以直接使用union all，来返回查询结果

举例：返回员工有部门，员工无部门，部门无员工三种情况的所有结果

会在107行数据的基础上增加16条部门无员工的数据

mysql> SELECT employee_id,department_name
    -> FROM employees e LEFT JOIN departments d
    -> ON e.`department_id` = d.`department_id`
    -> UNION ALL
    -> SELECT employee_id,department_name
    -> FROM employees e RIGHT JOIN departments d
    -> ON e.`department_id` = d.`department_id`
    -> WHERE e.`department_id` IS NULL;
+-------------+----------------------+
| employee_id | department_name      |
+-------------+----------------------+
|         178 | NULL                 |
|         200 | Administration       |
|         201 | Marketing            |
|         202 | Marketing            |
|         114 | Purchasing           |
|         115 | Purchasing           |
|         116 | Purchasing           |
|         117 | Purchasing           |
|         118 | Purchasing           |
|         119 | Purchasing           |
|         203 | Human Resources      |
|         120 | Shipping             |
|         121 | Shipping             |
|         122 | Shipping             |
|         123 | Shipping             |
|         124 | Shipping             |
|         125 | Shipping             |
|         126 | Shipping             |
|         127 | Shipping             |
|         128 | Shipping             |
|         129 | Shipping             |
|         130 | Shipping             |
|         131 | Shipping             |
|         132 | Shipping             |
|         133 | Shipping             |
|         134 | Shipping             |
|         135 | Shipping             |
|         136 | Shipping             |
|         137 | Shipping             |
|         138 | Shipping             |
|         139 | Shipping             |
|         140 | Shipping             |
|         141 | Shipping             |
|         142 | Shipping             |
|         143 | Shipping             |
|         144 | Shipping             |
|         180 | Shipping             |
|         181 | Shipping             |
|         182 | Shipping             |
|         183 | Shipping             |
|         184 | Shipping             |
|         185 | Shipping             |
|         186 | Shipping             |
|         187 | Shipping             |
|         188 | Shipping             |
|         189 | Shipping             |
|         190 | Shipping             |
|         191 | Shipping             |
|         192 | Shipping             |
|         193 | Shipping             |
|         194 | Shipping             |
|         195 | Shipping             |
|         196 | Shipping             |
|         197 | Shipping             |
|         198 | Shipping             |
|         199 | Shipping             |
|         103 | IT                   |
|         104 | IT                   |
|         105 | IT                   |
|         106 | IT                   |
|         107 | IT                   |
|         204 | Public Relations     |
|         145 | Sales                |
|         146 | Sales                |
|         147 | Sales                |
|         148 | Sales                |
|         149 | Sales                |
|         150 | Sales                |
|         151 | Sales                |
|         152 | Sales                |
|         153 | Sales                |
|         154 | Sales                |
|         155 | Sales                |
|         156 | Sales                |
|         157 | Sales                |
|         158 | Sales                |
|         159 | Sales                |
|         160 | Sales                |
|         161 | Sales                |
|         162 | Sales                |
|         163 | Sales                |
|         164 | Sales                |
|         165 | Sales                |
|         166 | Sales                |
|         167 | Sales                |
|         168 | Sales                |
|         169 | Sales                |
|         170 | Sales                |
|         171 | Sales                |
|         172 | Sales                |
|         173 | Sales                |
|         174 | Sales                |
|         175 | Sales                |
|         176 | Sales                |
|         177 | Sales                |
|         179 | Sales                |
|         100 | Executive            |
|         101 | Executive            |
|         102 | Executive            |
|         108 | Finance              |
|         109 | Finance              |
|         110 | Finance              |
|         111 | Finance              |
|         112 | Finance              |
|         113 | Finance              |
|         205 | Accounting           |
|         206 | Accounting           |
|        NULL | Treasury             |
|        NULL | Corporate Tax        |
|        NULL | Control And Credit   |
|        NULL | Shareholder Services |
|        NULL | Benefits             |
|        NULL | Manufacturing        |
|        NULL | Construction         |
|        NULL | Contracting          |
|        NULL | Operations           |
|        NULL | IT Support           |
|        NULL | NOC                  |
|        NULL | IT Helpdesk          |
|        NULL | Government Sales     |
|        NULL | Retail Sales         |
|        NULL | Recruiting           |
|        NULL | Payroll              |
+-------------+----------------------+
123 rows in set (0.01 sec) 

也可以左中图 union all 右上图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
A∪B-A∩B

最后一个图的情况对应右下图，右下图是返回两张表互不关联的部分，即去除交集后剩下的部分。可以看成是左中图+UNION ALL+ 右中图

举例：返回员工无部门，部门无员工的情况
mysql> SELECT employee_id,department_name
    -> FROM employees e LEFT JOIN departments d
    -> ON e.`department_id` = d.`department_id`
    -> WHERE d.`department_id` IS NULL
    -> UNION ALL
    -> SELECT employee_id,department_name
    -> FROM employees e RIGHT JOIN departments d
    -> ON e.`department_id` = d.`department_id`
    -> WHERE e.`department_id` IS NULL;
+-------------+----------------------+
| employee_id | department_name      |
+-------------+----------------------+
|         178 | NULL                 |
|        NULL | Treasury             |
|        NULL | Corporate Tax        |
|        NULL | Control And Credit   |
|        NULL | Shareholder Services |
|        NULL | Benefits             |
|        NULL | Manufacturing        |
|        NULL | Construction         |
|        NULL | Contracting          |
|        NULL | Operations           |
|        NULL | IT Support           |
|        NULL | NOC                  |
|        NULL | IT Helpdesk          |
|        NULL | Government Sales     |
|        NULL | Retail Sales         |
|        NULL | Recruiting           |
|        NULL | Payroll              |
+-------------+----------------------+
17 rows in set (0.00 sec)

总结：

左中图：
#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

右中图:
#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

左下图：
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union 
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;


右下图：
#实现A∪B - A∩B 或   (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
Salute-Y
关注
  • 3
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql数据库的基本操作语法
12-05
通常我们并不对视图的数据做修改操作，因为视图是一张虚拟的表，它并不存储实际数据。如果想让视图不被修改，可以用with check option来完成限制。 create or replace view view_temp as select * from temp with ...
MySQL的7种join，交集，并集，差集。
chaochao2113的博客
10-15 6768
MySQL左外连接,右外连接,内连接,全外连接
pgsql 取并集交集差集
qq_42715450的博客
08-15 4275
postgresql 取交集并集差集
【精】一张图搞懂是SQL join，图解SQL的7种JOIN
无知是恶
09-21 3086
通过集合的方式来理解SQL的七种join，更加直观易懂下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。
SQL取并集 差集 交集
热门推荐
now
05-15 1万+
SQL并集（UNIOIN 去重 UNIOIN ALL不去重）交集(Intersect)差集（EXCEPT） MySQL仅支持并集，intersect用join替换，except用not in替换其他要用其他方式替代 1.UNION形成并集 UNION可以对两个或多个结果集进行连接，形成“并集”。 子结果集所有的记录组合在一起形成新的结果集。1、限定条件 要是用UNION来连接结果集，有4个限定条件。 (1)、子结果集要具有相同的结构。 (2)、字结果集的列数必须相同。 (3)、子结果集对应的数据
SQL并集、交集、差集使用
最新发布
qq_25775675的博客
11-21 3420
SQL语句实现数据的并集（union）、交集（intersect）、差集（except)。
mysql sql99语法 内连接非等值连接详解
01-19
JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`lowest_sal`; #案例：查询每个工资级别的个数，并且按级别降序 SELECT COUNT(*),grade_level FROM job_grades j JOIN employees e ON e.`...
MySQL进阶十：DML-更新
01-20
目录 DML-UPDATE 语法 1、修改单表的记录 2、修改多表的记录 文章涉及到的MySQL、SQLyog的配置安装，请参考 MySQL配置+SQLyog...sql99语法 update 表1 别名 inner/left/right join 表2 别名 on 连接条件 set 列=值，.
mysql 8.0官方文档
05-08
MySQL 8.0支持标准的SQL92/99，并引入了一些扩展。 - 基本操作包括：CREATE DATABASE创建数据库，CREATE TABLE定义表结构，INSERT插入数据，SELECT查询数据，UPDATE更新数据，DELETE删除数据。 3. **数据类型**： ...
82分-云数据库 MySQL 产品认证(1).docx
12-17
1. 云数据库 MySQL 产品认证中，DTS 腾讯云 MySQL 之间的迁移过程中，源端可以使用 root 账户，迁移过程中需要对用户进行授权，但不能从基础版迁移到金融版 MySQL，也不能进行同版本之间的数据迁移。 2. 云数据库 ...
sql 取交集并集差集 - sql中去掉字段的所有空格-sql 特殊字符替换
qq_40711092的博客
12-08 1358
mysql #取并集 union 会去重 select * from c_user where `password`='123' UNION select * from c_user where did = 1 #取并集 union 不会去重 select * from c_user where `password`='123' UNION all select * from c_user where did = 1 #取交集 SELECT a.username,a.`password` FROM c
SQL外连接之交集、并集、差集查询
不曾走远的博客
08-05 4147
SQL外连接之交集、并集、差集查询
SQL语句求交集、并集、差集
jsxztshaohaibo的博客
04-07 9865
A、B的交集： select * from A inner jion B on A.col = B.col A、B 的对称的差集： select * from A full outer join B on A.col = B.col where A.col is null or B.col is null 或者 select * from A where B.col is null join select *from B where A.col is null A、...
MYSQL--基础--11--join理解
zhou920786312的博客
08-06 197
一、数据初始化 SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '课程id', `name
7种join理论
qq_42449963的博客
10-09 3485
以上7种join，从左到右从上到下分别是左连接、右连接、左连接之后去除共有部分、内连接、右连接之后去除共有部分、全连接、全连接之后去除共有部分 接下来我使用mysql数据库来建表进行解释： 首先建立表a： 之后建立表b： 请注意：表a和表b的字段名称有一个相同，另外一个不同，其中两个表中的id也是一个相同，另外一个不同，我们接下来将用这两张表来模拟以上7中join的连接情况； 1、左连接 select * from a left join b on a.id = b.id 2、右连接 select *.
mysql利用join查询 交集 差集 并集
传说中胖子
06-24 915
pgsql查询结果集取交集、并集、差集操作
cainiaobulan的博客
12-13 3694
pgsql对结果集进行交、并、差集操作
SQL中的各种join操作
蓝天⊙白云的博客
12-20 481
返回左边表中的所有行，即使右边表中没有行与之匹配，左边的行依然显示，右边没有匹配尚的显示为null。通俗理解，就是取两张表的并集；mysql中不支持该语法，但是可以采用UNION方式完成。和左连接正好相反，返回右边表的所有行，即使左边没有行与之匹配，未匹配上的显示null。MySQL自行根据相同的字段名判断并完成连接，不需要指定条件；内连接查询两张表直接的交集部分，只保留两张表都有的字段。如果查询左表独有，使用以下sql。查询右边表独有的数据。
SQL中的并、交、差运算
qq_36594235的博客
07-07 7751
SQL中的并、交、差运算 1、交（对应于集合中的交集操作，A∩B) 注：返回查询结果中相同的部分既他们的交集，想保留所有的重复，必须用INTERSECT ALL代替INTERSECT （select * from table1） intersect （select * from table2） 2、差集（对应于集合中的差集操作，A-B） 注：返回在第一个查询结果中与第二个查询结果不相同的那部分行记录, 即两个结果的差集，EXCEPT运算自动去除重复，如果想保留所有的重复，必须用EXCEPT ALL代替EXC
MySQL讲义.pdf
02-14
- SQL99语法：包括自连接、外连接的更简洁写法。 10、子查询 - 子查询在where语句中的使用：子查询作为条件，用于筛选满足特定条件的行。 这份讲义全面覆盖了MySQL的基础操作，包括数据库管理、数据查询、数据...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值