专栏首页SQL实现编写 SQL 的排除联接

编写 SQL 的排除联接

有两个表,就叫源表和目标表吧。它们有一个相同的字段,通过该字段可以把源表和目标表关联在一起,我们希望从源表中检索到的记录里的关联字段的值没有存在目标表中。举个例子,源表 dept,目标表 emp,获取 dept 表中部门编号不在 emp 表中的记录。在检查两张表的数据后,我们发现 emp 表中没有部门编号 40 的数据。

图1 emp 表的数

图2 dept 表的数据

实现这种的查询的方法有很多,不同的实现方式的性能也会不一样。我们就来看看都有哪些方法?

NOT IN

SELECT 
  * 
FROM
  dept 
WHERE deptno NOT IN 
  (SELECT 
    deptno 
  FROM
    emp)

这种实现方式需要注意一个点,就是在 not in 里面不能出现 NULL,如果出现 NULL 就会查不到结果。比如下面这条 SQL,没有数据返回。

SELECT 
  dname 
FROM
  dept 
WHERE deptno NOT IN 
  (SELECT 
    deptno 
  FROM
    emp 
  UNION ALL 
  SELECT 
    NULL)

为什么是这样呢?

因为在逻辑运算中,涉及到 NULL 的操作的结果仍为 NULLnot in 可以改写成 or 的形式,比如 deptno not in(10,NULL) 展开成 or 的表达式是:not (deptno = 10 or deptno = NULL),最终的表达式是 not NULL

NOT EXISTS

使用 not exists 可以避免由于目标表的关联列上出现 NULL 而查不出数据。

WITH e AS 
(SELECT 
  deptno 
FROM
  emp 
UNION ALL 
SELECT 
  NULL) 
SELECT 
  * 
FROM
  dept 
WHERE NOT EXISTS 
  (SELECT 
    NULL 
  FROM
    e 
  WHERE e.deptno = dept.deptno)

使用 not exists 的 SQL 的一般形式:

SELECT 
  目标列 
FROM
  源表 
WHERE NOT EXISTS 
  (SELECT 
    NULL 
  FROM
    目标表 
  WHERE 关联字段)

在 MySQL 5.6 之前,子查询的性能表现得比较差,因而就有人想着把子查询改成连接的方式以提高查询性能。

LEFT JOIN

通常,我们会想到使用 NOT INNOT EXISTS 做排除操作。其实,使用 LEFT JOIN 也可以达到相同的目的。

SELECT 
  d.* 
FROM
  dept d 
  LEFT JOIN emp e 
    ON e.deptno = d.deptno 
WHERE e.deptno IS NULL 

对于表达式 a left join b ,不管 b 表中是否有数据可以和 a 表匹配得上,a 表总是能返回所有数据。如果 b 表中没有数据能匹配得上 a 表,在查询结果中会使用 NULL 填充 b 表的列。因此,通过过滤条件 b.关联列 is NULL 可以找到只存在于 a 表中的数据。

总结

  1. 使用 not in 时要考虑到排除的值中是否有 NULL ,如果有,需要提前做过滤处理。
  2. not existsleft join 都可以用来做排除操作,可以任选一种方式实现,如果 SQL 的性能表现不佳,则可以换另外一种方式试试。

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 不用 UNION 操作符实现 UNION 的效果

    当我们要合并两个表或者多个表的结果时,可使用 UNION ALL 或者 UNION 操作符, UNION 和 UNION ALL 的区别在于前者会对结果集去重...

    白日梦想家
  • SQL 窗口函数

    MYSQL 从 8.0.2 版本起开始支持窗口函数,那么在窗口函数没出来之前,我们要实现类似的功能该怎么做呢?

    白日梦想家
  • SQL 行转列+窗口函数的实例

    今天继续和大家分享 HackerRank 上的 SQL 编程挑战的解题思路,这一次的题目叫做“Occupations”,属于中等难度级别,答案提交的成功率在 9...

    白日梦想家
  • MySQL information_schema详解 FILES

    它提供InnoDB数据文件的信息,如果是NDB,它提供了有关存储了NDB集群磁盘数据表的文件的信息。

    bsbforever
  • WinExec, ShellExecute,CreateProcess 区别

    本文转载自WinExec, ShellExecute,CreateProcess 区别 其中以WinExec最为简单,ShellExecute比WinExec...

    ccf19881030
  • py12306:你的 12306 购票助手(支持集群,多账号,多任务购票)

    验证码可以本地识别,所用的模型和算法均来自 https://github.com/zhaipro/easy12306 十分感谢! 验证码识别已迁移到服务器进行识...

    良月柒
  • “高价收购”微信号,180元/天?

    上周,老舅神秘兮兮地把我拉到一边,“你们微信号是不是很值钱啊?有个人跟我说,可以高价收我的微信号,如果我不想卖,还可以租给他,还给180元/天呢。我就问问是不是...

    用户6966869
  • 人脸识别技术一夜躺枪后 百度、旷视、商汤、云从等是这样“技术”回应的

    昨晚的央视315晚会上,人脸识别技术被曝存在安全隐患。不少观众看到主持人在现场技术人员支持下,仅凭两部手机、一张随机正面照片及一个换脸App,分别就一张”眨眨眼...

    AI科技评论
  • 学习python DAY 01-------列表-----简单的数据结构

    CODE-S
  • Pandas-4. Panel

    从warning信息可知,该方法已经废弃,建议用MultiIndex on a DataFrame来处理3D信息。

    悠扬前奏

扫码关注云+社区

领取腾讯云代金券