连接查询你一定不陌生，但你知道还有半连接和反连接吗？

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



 

文章目录

    半连接
    反连接
    总结

大家好，我是只谈技术不剪发的 Tony 老师。在关系数据库中，连接查询（JOIN）可以从两个或多个表中获取相关的数据。我们熟悉的连接查询包括内连接、左/右/全外连接、交叉连接等。

除此之外，还有两种特殊的连接查询：半连接（Semi Join）和反连接（Anti Join）。由于 SQL 标准没有定义这两种连接查询语法，而是通过子查询的方式实现相同的效果；因此，本文就来介绍一下它们的概念和作用。

📝本文内容适用于各种数据库，包括 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 以及 SQLite 等。

半连接

半连接返回左表中与右表至少匹配一次的数据行，通常体现为 EXISTS 或者 IN 子查询。半连接的示意图如下：

Semi_Join
table1 中的 id = 2 在 table2 中没有对应的数据，所以连接的结果不包含该记录。

半连接只会返回左表中的数据，右表只用于条件判断。另外，即使右表中存在多个匹配的数据，左边中的数据只返回一次。半连接通常用于存在性判断，例如哪些顾客购买了产品，而不需要知道他们购买的具体产品和数量。

以下语句用于查找拥有员工的部门（示例数据）：

SELECT *
FROM department d
WHERE EXISTS (SELECT 1 FROM employee WHERE dept_id = d.dept_id);

  1|行政管理部   |
  2|人力资源部   |
  3|财务部      |
  4|研发部      |
  5|销售部      |

以上语句也可以使用 IN 或者 =ANY 操作符加上子查询来实现：

SELECT *
FROM department d
WHERE dept_id IN (SELECT dept_id FROM employee);

SELECT *
FROM department d
WHERE dept_id = ANY (SELECT dept_id FROM employee);

虽然 SQL 没有定义 SEMI JOIN 关键字，但是我们可以通过数据库的执行计划查看相关的信息。以下是 MySQL 数据库中的执行计划：

EXPLAIN ANALYZE
SELECT *
FROM department d
WHERE EXISTS (SELECT 1 FROM employee WHERE dept_id = d.dept_id);

-> Nested loop semijoin (cost=5.36 rows=30) (actual time=0.056…0.097 rows=5 loops=1)
-> Table scan on d (cost=0.85 rows=6) (actual time=0.034…0.042 rows=6 loops=1)
-> Index lookup on employee using idx_emp_dept (dept_id=d.dept_id) (cost=1.67 rows=5) (actual time=0.008…0.008 rows=1 loops=6)

其中，Nested loop semijoin 表示这是一个嵌套循环的半连接查询。

📝关于各种数据库执行计划的查看方式和结果解释，可以参考这篇文章。

半连接也可以使用内连接实现，例如：

SELECT DISTINCT d.*
FROM department d
JOIN employee e ON e.dept_id = d.dept_id;

首先通过内连接获取所有满足条件的数据，然后执行 DISTINCT 操作去除重复值；显然这种方式不如半连接效率高，不过大多数数据库可以实现这两者的等价转换。
反连接

反连接返回左表中与右表不匹配的数据行，通常体现为 NOT EXISTS 或者 NOT IN 子查询。反连接的逻辑与半连接正好相反，示意图如下：

Anti_Join
table1 中只有 id = 2 在 table2 中没有对应的数据，所以连接的结果返回了该记录。

反连接只会返回左表中的数据，右表只用于条件判断。反查询常见的应用包括：查找没有员工的部门信息，或者没有购买任何产品的顾客信息等。

例如，以下语句返回了没有员工的部门：

SELECT *
FROM department d
WHERE NOT EXISTS (SELECT 1 FROM employee WHERE dept_id = d.dept_id);

  6|保卫部      |

以上语句也可以使用 NOT IN 或者 !=ALL 操作符加上子查询来实现：

SELECT *
FROM department d
WHERE dept_id NOT IN (SELECT dept_id FROM employee);

SELECT *
FROM department d
WHERE dept_id !=ALL (SELECT dept_id FROM employee);

使用 NOT IN 或者 !=ALL 操作符时需要注意子查询中可能出现的 NULL 值。例如：

SELECT *
FROM department d
WHERE dept_id NOT IN (1, NULL, 2);

以上查询不会返回任何结果，因为它的查询条件实际上等价于：

SELECT *
FROM department d
WHERE dept_id !=1 AND dept_id != NULL AND dept_id != 2;

其中，dept_id != NULL 导致所有数据都不会满足条件；因为任何数值和 NULL 进行比较的结果都是未知（Unknown），也就是不为真（True）。

虽然 SQL 没有定义 ANTI JOIN 关键字，但是我们可以通过数据库的执行计划查看相关的信息。以下是 MySQL 数据库中的执行计划：

EXPLAIN ANALYZE
SELECT *
FROM department d
WHERE NOT EXISTS (SELECT 1 FROM employee WHERE dept_id = d.dept_id);

-> Nested loop antijoin (cost=5.36 rows=30) (actual time=0.245…0.255 rows=1 loops=1)
-> Table scan on d (cost=0.85 rows=6) (actual time=0.135…0.151 rows=6 loops=1)
-> Index lookup on employee using idx_emp_dept (dept_id=d.dept_id) (cost=1.67 rows=5) (actual time=0.016…0.016 rows=1 loops=6)

其中，Nested loop antijoin 表示这是一个嵌套循环的反连接查询。

📝关于各种数据库执行计划的查看方式和结果解释，可以参考这篇文章。

反连接也可以使用外连接实现，例如：

SELECT DISTINCT d.*
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id
WHERE e.dept_id IS NULL;

首先通过左外连接获取所有满足条件的数据，然后使用 WHERE 条件找出右表中不存在的数据，最后执行 DISTINCT 操作去除重复值；显然这种方式不如反连接效率高，不过大多数数据库可以实现这两者的等价转换。
总结

数据库中除了常见的内连接、左/右/全外连接、交叉连接等类型之外，还有两种特殊的连接查询：半连接和反连接。本文介绍了这两种连接的原理和它们在数据库中的等价实现。