精品久久久中文字幕人妻,韩国一级成a人片在线

在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

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

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

文章目錄

    半連接
    反連接
    總結(jié)

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

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

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

半連接

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

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

半連接只會(huì)返回左表中的數(shù)據(jù)，右表只用于條件判斷。另外，即使右表中存在多個(gè)匹配的數(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|財(cái)務(wù)部      |
  4|研發(fā)部      |
  5|銷售部      |

以上語句也可以使用 IN 或者 =ANY 操作符加上子查詢來實(shí)現(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 關(guān)鍵字，但是我們可以通過數(shù)據(jù)庫的執(zhí)行計(jì)劃查看相關(guān)的信息。以下是 MySQL 數(shù)據(jù)庫中的執(zhí)行計(jì)劃：

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

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

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

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

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

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

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

反連接只會(huì)返回左表中的數(shù)據(jù)，右表只用于條件判斷。反查詢常見的應(yīng)用包括：查找沒有員工的部門信息，或者沒有購買任何產(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 操作符加上子查詢來實(shí)現(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 操作符時(shí)需要注意子查詢中可能出現(xiàn)的 NULL 值。例如：

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

以上查詢不會(huì)返回任何結(jié)果，因?yàn)樗牟樵儣l件實(shí)際上等價(jià)于：

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

其中，dept_id != NULL 導(dǎo)致所有數(shù)據(jù)都不會(huì)滿足條件；因?yàn)槿魏螖?shù)值和 NULL 進(jìn)行比較的結(jié)果都是未知（Unknown），也就是不為真（True）。

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

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

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

反連接也可以使用外連接實(shí)現(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 操作去除重復(fù)值；顯然這種方式不如反連接效率高，不過大多數(shù)數(shù)據(jù)庫可以實(shí)現(xiàn)這兩者的等價(jià)轉(zhuǎn)換。
總結(jié)

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