連接查詢你一定不陌生，但你知道還有半連接和反連接嗎？

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        半連接
        反連接
        總結

大家好，我是只談技術不剪發(fā)的 Tony 老師。在關系數(shù)據(jù)庫中，連接查詢（JOIN）可以從兩個或多個表中獲取相關的數(shù)據(jù)。我們熟悉的連接查詢包括內(nèi)連接、左/右/全外連接、交叉連接等。

除此之外，還有兩種特殊的連接查詢：半連接（Semi Join）和反連接（Anti Join）。由于 SQL 標準沒有定義這兩種連接查詢語法，而是通過子查詢的方式實現(xiàn)相同的效果；因此，本文就來介紹一下它們的概念和作用。

    ??本文內(nèi)容適用于各種數(shù)據(jù)庫，包括 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 以及 SQLite 等。

半連接

半連接返回左表中與右表至少匹配一次的數(shù)據(jù)行，通常體現(xiàn)為 EXISTS 或者 IN 子查詢。半連接的示意圖如下：


table1 中的 id = 2 在 table2 中沒有對應的數(shù)據(jù)，所以連接的結果不包含該記錄。

半連接只會返回左表中的數(shù)據(jù)，右表只用于條件判斷。另外，即使右表中存在多個匹配的數(shù)據(jù)，左邊中的數(shù)據(jù)只返回一次。半連接通常用于存在性判斷，例如哪些顧客購買了產(chǎn)品，而不需要知道他們購買的具體產(chǎn)品和數(shù)量。

以下語句用于查找擁有員工的部門（示例數(shù)據(jù)）：

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

dept_id|dept_name  |
-------|-----------|
      1|行政管理部   |
      2|人力資源部   |
      3|財務部      |
      4|研發(fā)部      |
      5|銷售部      |

 

以上語句也可以使用 IN 或者 =ANY 操作符加上子查詢來實現(xiàn)：

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 關鍵字，但是我們可以通過數(shù)據(jù)庫的執(zhí)行計劃查看相關的信息。以下是 MySQL 數(shù)據(jù)庫中的執(zhí)行計劃：

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 表示這是一個嵌套循環(huán)的半連接查詢。

    ??關于各種數(shù)據(jù)庫執(zhí)行計劃的查看方式和結果解釋，可以參考這篇文章。

半連接也可以使用內(nèi)連接實現(xiàn)，例如：

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

 

首先通過內(nèi)連接獲取所有滿足條件的數(shù)據(jù)，然后執(zhí)行 DISTINCT 操作去除重復值；顯然這種方式不如半連接效率高，不過大多數(shù)數(shù)據(jù)庫可以實現(xiàn)這兩者的等價轉換。
反連接

反連接返回左表中與右表不匹配的數(shù)據(jù)行，通常體現(xiàn)為 NOT EXISTS 或者 NOT IN 子查詢。反連接的邏輯與半連接正好相反，示意圖如下：


table1 中只有 id = 2 在 table2 中沒有對應的數(shù)據(jù)，所以連接的結果返回了該記錄。

反連接只會返回左表中的數(shù)據(jù)，右表只用于條件判斷。反查詢常見的應用包括：查找沒有員工的部門信息，或者沒有購買任何產(chǎn)品的顧客信息等。

例如，以下語句返回了沒有員工的部門：

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

dept_id|dept_name  |
-------|-----------|
      6|保衛(wèi)部      |

 

以上語句也可以使用 NOT IN 或者 !=ALL 操作符加上子查詢來實現(xiàn)：

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 操作符時需要注意子查詢中可能出現(xiàn)的 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 導致所有數(shù)據(jù)都不會滿足條件；因為任何數(shù)值和 NULL 進行比較的結果都是未知（Unknown），也就是不為真（True）。

雖然 SQL 沒有定義 ANTI JOIN 關鍵字，但是我們可以通過數(shù)據(jù)庫的執(zhí)行計劃查看相關的信息。以下是 MySQL 數(shù)據(jù)庫中的執(zhí)行計劃：

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 表示這是一個嵌套循環(huán)的反連接查詢。

    ??關于各種數(shù)據(jù)庫執(zhí)行計劃的查看方式和結果解釋，可以參考這篇文章。

反連接也可以使用外連接實現(xiàn)，例如：

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

 

首先通過左外連接獲取所有滿足條件的數(shù)據(jù)，然后使用 WHERE 條件找出右表中不存在的數(shù)據(jù)，最后執(zhí)行 DISTINCT 操作去除重復值；顯然這種方式不如反連接效率高，不過大多數(shù)數(shù)據(jù)庫可以實現(xiàn)這兩者的等價轉換。
總結

數(shù)據(jù)庫中除了常見的內(nèi)連接、左/右/全外連接、交叉連接等類型之外，還有兩種特殊的連接查詢：半連接和反連接。本文介紹了這兩種連接的原理和它們在數(shù)據(jù)庫中的等價實現(xiàn)。

如果覺得文章對你有用，歡迎關注??、評論??、點贊??！