快捷導(dǎo)航

MySQL關(guān)鍵字IN與EXISTS的使用與區(qū)別詳解

更新時(shí)間：2024年09月05日 11:15:16 作者：Roc.Chang

in和exists是兩種常用的條件查詢關(guān)鍵字,兩種常用于子查詢,它們在某些情況下可以互換使用,但它們的工作方式和效率可能會(huì)有所不同,這篇文章主要給大家介紹了關(guān)于MySQL關(guān)鍵字IN與EXISTS的使用與區(qū)別的相關(guān)資料,需要的朋友可以參考下

1. IN & NOT IN

IN：IN用于判斷某個(gè)字段的值是否存在于給定的值列表中，常用于簡單的列表匹配?？梢允褂脝蝹€(gè)值，也可以使用一個(gè)由多個(gè)值組成的列表，也可以是一個(gè)子查詢。以下是IN關(guān)鍵詞的示例用法：

1.1 基本使用

?? 語法一：

SELECT * FROM table_name 
WHERE column_name IN (value1, value2, value3);

-- 示例
SELECT * FROM employees WHERE department_id IN (1, 2, 3);

這將返回table_name表中滿足條件的行，其中列column_name的值在給定的值列表(value1, value2, value3)中。

?? 語法二：

SELECT * FROM table_name 
WHERE column_name IN (select column_name_b from table_name_b  WHERE condition );

-- 示例
SELECT * FROM employees WHERE department_id IN 
	(SELECT id FROM departments WHERE name = 'WorkDog');

其實(shí)與上面的用法是一樣的，只是將給定的值列表換成了 table_name_b 表中的某個(gè)字段的值。先查出對應(yīng)字段的所有值，然后再與前面表 table_name 的 column_name 字段進(jìn)行值比較，返回table_name表中滿足條件的行。

1.2 工作原理

在MySQL中，IN 語句用于檢查某個(gè)值是否在指定的列表或子查詢結(jié)果集中。IN 語句的工作原理包括處理靜態(tài)值列表和子查詢結(jié)果集。在不同的情況下，MySQL會(huì)采用不同的策略來執(zhí)行 IN 語句。下面詳細(xì)解釋 IN 語句的工作原理。

1.2.1 靜態(tài)值列表的 IN 語句

對于靜態(tài)值列表，MySQL會(huì)將列表中的每個(gè)值與目標(biāo)列的值進(jìn)行比較。如果目標(biāo)值在列表中，條件為真。

?? 假設(shè)有兩個(gè)表 employees ，查詢指定 department_id 的部門：

SELECT * 
FROM employees 
WHERE department_id IN (1, 2, 3);

?? 執(zhí)行過程如下：

解析查詢：MySQL解析查詢語句。
執(zhí)行計(jì)劃：MySQL生成執(zhí)行計(jì)劃，決定如何訪問 employees 表。
逐行掃描：對于 employees 表中的每一行，MySQL檢查 department_id 列是否為 1、2 或 3。
返回結(jié)果：匹配的行被返回。

在這個(gè)過程中，MySQL對每一行執(zhí)行簡單的比較操作。這種情況下的 IN 語句等價(jià)于多個(gè) OR 條件。

SELECT * 
FROM employees 
WHERE department_id = 1 
   OR department_id = 2 
   OR department_id = 3;

1.2.2 子查詢的 IN 語句

當(dāng) IN 語句包含子查詢時(shí)，MySQL必須先執(zhí)行子查詢并獲取結(jié)果集，然后將主查詢中的值與子查詢結(jié)果集中的值進(jìn)行比較。

?? 示例：

SELECT * 
FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE name = 'WorkDog');

?? 執(zhí)行過程如下：

解析查詢：MySQL解析主查詢和子查詢。
執(zhí)行子查詢：MySQL執(zhí)行子查詢 SELECT id FROM departments WHERE name = 'WorkDog'，生成結(jié)果集。
緩存結(jié)果集：將子查詢的結(jié)果集緩存到內(nèi)存中。
執(zhí)行主查詢：MySQL生成主查詢的執(zhí)行計(jì)劃。
逐行掃描：對于 employees 表中的每一行，MySQL檢查 department_id 列是否在子查詢結(jié)果集中。
返回結(jié)果：匹配的行被返回。

在這種情況下，子查詢的執(zhí)行方式會(huì)影響整體查詢的性能。如果子查詢結(jié)果集較大，MySQL可能會(huì)使用臨時(shí)表來存儲結(jié)果集，并使用索引來加快查找速度。

?? 結(jié)果集緩存

當(dāng)使用 IN 子查詢時(shí)，MySQL會(huì)將子查詢的結(jié)果集緩存到內(nèi)存中以加快主查詢的執(zhí)行。對于非常大的結(jié)果集，這可能會(huì)導(dǎo)致內(nèi)存占用過多。在這種情況下，可以考慮使用臨時(shí)表或其他優(yōu)化方法來降低內(nèi)存使用。

1.3 相關(guān)優(yōu)化

如果子查詢返回的結(jié)果集較大，使用 EXISTS 可能會(huì)更有效，因?yàn)?nbsp;EXISTS 會(huì)在找到匹配的行后立即停止子查詢的執(zhí)行。
靜態(tài)值列表：確保在用于比較的列上有適當(dāng)?shù)乃饕?。例如，上面?1.2.1 例子 department_id 列上創(chuàng)建索引。
子查詢：確保子查詢中使用的列上有適當(dāng)?shù)乃饕?。例如，上面?1.2.2 例子中 departments.id 和 departments.name 列上創(chuàng)建索引。

將 IN 子查詢轉(zhuǎn)換為 JOIN 操作，例如：

-- 使用IN子查詢
SELECT * 
FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE name = 'WorkDog');

-- 轉(zhuǎn)換為JOIN
SELECT e.* 
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'WorkDog';

2. EXISTS & NOT EXISTS

EXISTS：EXISTS用于判斷是否存在滿足子查詢條件的結(jié)果，常用于復(fù)雜的條件檢查。子查詢可以是一個(gè)查詢語句，返回一個(gè)結(jié)果集。

2.1 基本使用

以下是EXISTS關(guān)鍵詞的示例用法， NOT EXISTS 同理：

SELECT column_name FROM table_name1 
WHERE EXISTS (
	SELECT column_name FROM table_name2 WHERE condition
);

-- 示例
SELECT * FROM employees e WHERE EXISTS 
	(SELECT 1 FROM departments d WHERE d.name = 'WorkDog' AND e.department_id = d.id);

這將返回table_name1表中滿足EXISTS子查詢條件的行，子查詢是在table_name2表中的一個(gè)查詢。如果子查詢返回結(jié)果集，則認(rèn)為條件滿足。

2.2 工作原理

當(dāng) MySQL 處理一個(gè)包含 EXISTS 子查詢的查詢時(shí)，它會(huì)逐條掃描外表的每一行，并對每一行執(zhí)行一次子查詢。如果子查詢返回至少一行結(jié)果，那么 EXISTS 條件就滿足，主查詢的那一行就會(huì)被包含在最終結(jié)果集中，否則只查詢的那一行就會(huì)被舍棄。

?? 假設(shè)有兩個(gè)表 employees 和 departments，希望找到所有在特定部門（例如 WorkDog）工作的員工：

SELECT * 
FROM employees e 
WHERE EXISTS (
    SELECT 1 
    FROM departments d 
    WHERE e.department_id = d.id 
      AND d.name = 'WorkDog'
);

??執(zhí)行過程如下：

初始化：MySQL 初始化主查詢，開始掃描 employees 表。
逐行掃描外表：對 employees 表中的每一行，執(zhí)行以下步驟：
- 讀取一行：讀取當(dāng)前行的 department_id。
- 執(zhí)行子查詢：針對當(dāng)前行的 department_id 執(zhí)行子查詢
```
SELECT 1 
FROM departments d 
WHERE e.department_id = d.id 
  AND d.name = 'WorkDog'
```
- 檢查子查詢結(jié)果：如果子查詢返回至少一行結(jié)果，則 EXISTS 條件滿足（true），這一行會(huì)被包含在結(jié)果集中。如果子查詢不返回任何結(jié)果，則 EXISTS 條件不滿足(false)，這一行會(huì)被排除在結(jié)果集之外。
繼續(xù)掃描：重復(fù)步驟2，直到掃描完 employees 表的所有行。
返回結(jié)果：將滿足 EXISTS 條件的所有行作為結(jié)果返回。

2.3 相關(guān)優(yōu)化

由于 EXISTS 子查詢對于外表中的每一行都會(huì)執(zhí)行一次，這意味著子查詢的性能對于整個(gè)查詢的性能至關(guān)重要。

索引使用：確保子查詢中的過濾條件上有適當(dāng)?shù)乃饕＿@可以顯著減少子查詢的執(zhí)行時(shí)間。
簡化子查詢：盡量簡化子查詢，使其只返回需要的最小數(shù)據(jù)量。例如，使用 SELECT 1 而不是 SELECT *。
避免計(jì)算：避免在子查詢中進(jìn)行復(fù)雜的計(jì)算，可以在外部查詢中處理這些計(jì)算。

3. 兩者區(qū)別

(1) 用法：

IN 關(guān)鍵字可以與常量列表一起使用，也可以與子查詢一起使用。適用于在某個(gè)字段的值與給定值列表之間進(jìn)行匹配。它是基于字段值與值列表進(jìn)行比較的操作符。
EXISTS 關(guān)鍵字只能與子查詢一起使用。適用于檢查是否存在滿足子查詢條件的結(jié)果。它是基于子查詢是否返回結(jié)果集進(jìn)行判斷的條件。

(2) 功能：

IN 關(guān)鍵字用于在一個(gè)查詢中匹配一個(gè)值是否存在于一個(gè)列表中。
EXISTS 關(guān)鍵字用于檢查子查詢是否返回任何行。

(3) 子查詢結(jié)果：

IN 關(guān)鍵字的子查詢返回的結(jié)果集可以是給定的多個(gè)值列表，或者是一個(gè)單獨(dú)的查詢語句（返回結(jié)果必須只有一個(gè)字段）。
EXISTS 關(guān)鍵字的子查詢通常返回一個(gè)布爾值，表示子查詢是否返回了任何行。

(4) 性能:

IN 通常比 EXISTS 更快，尤其是在值列表較小時(shí)。因?yàn)樗恍枰獔?zhí)行額外的邏輯來檢查是否存在結(jié)果。
EXISTS 關(guān)鍵字在處理大量數(shù)據(jù)時(shí)比 IN 關(guān)鍵字更高效。這是因?yàn)?nbsp;EXISTS 只需要找到匹配的行，并返回結(jié)果，而不需要返回整個(gè)列表；性能可能會(huì)受到子查詢的復(fù)雜性和數(shù)據(jù)量的影響。

(5) 空值處理：

IN和EXISTS對待空值的方式不同。

使用IN時(shí)，如果給定的值列表中包含空值，將無法通過等值比較來匹配到空值。
而EXISTS則可以判斷子查詢中是否存在空值結(jié)果。

在選擇使用 IN 還是 EXISTS 關(guān)鍵字時(shí)，需要根據(jù)具體的查詢需求和數(shù)據(jù)情況進(jìn)行考慮。如果只是簡單的匹配值是否在列表中，可以使用 IN。如果需要根據(jù)子查詢的返回結(jié)果來決定外部查詢的結(jié)果，或者需要處理大量數(shù)據(jù)，那么使用 EXISTS 可能更為適合。