1. IN & NOT IN

IN：IN用于判斷某個字段的值是否存在于給定的值列表中，常用于簡單的列表匹配?？梢允褂脝蝹€值，也可以使用一個由多個值組成的列表，也可以是一個子查詢。以下是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');

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

1.2 工作原理

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

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

對于靜態(tài)值列表，MySQL會將列表中的每個值與目標列的值進行比較。如果目標值在列表中，條件為真。

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

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

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

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

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

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

1.2.2 子查詢的 IN 語句

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

?? 示例：

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í)行計劃。
• 逐行掃描：對于 employees 表中的每一行，MySQL檢查 department_id 列是否在子查詢結(jié)果集中。
• 返回結(jié)果：匹配的行被返回。

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

?? 結(jié)果集緩存

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

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

• 如果子查詢返回的結(jié)果集較大，使用 EXISTS 可能會更有效，因為 EXISTS 會在找到匹配的行后立即停止子查詢的執(zhí)行。
• 靜態(tài)值列表：確保在用于比較的列上有適當?shù)乃饕＠?，上面?1.2.1 例子 department_id 列上創(chuà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ù)雜的條件檢查。子查詢可以是一個查詢語句，返回一個結(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表中的一個查詢。如果子查詢返回結(jié)果集，則認為條件滿足。

2.2 工作原理

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

?? 假設(shè)有兩個表 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í)行以下步驟：
  • 讀取一行：讀取當前行的 department_id。
  • 執(zhí)行子查詢：針對當前行的 department_id 執(zhí)行子查詢

  SELECT 1 
  FROM departments d 
  WHERE e.department_id = d.id 
    AND d.name = 'WorkDog'
  

  • 檢查子查詢結(jié)果：如果子查詢返回至少一行結(jié)果，則 EXISTS 條件滿足（true），這一行會被包含在結(jié)果集中。如果子查詢不返回任何結(jié)果，則 EXISTS 條件不滿足(false)，這一行會被排除在結(jié)果集之外。
• 繼續(xù)掃描：重復(fù)步驟2，直到掃描完 employees 表的所有行。
• 返回結(jié)果：將滿足 EXISTS 條件的所有行作為結(jié)果返回。

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

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

• 索引使用：確保子查詢中的過濾條件上有適當?shù)乃饕?。這可以顯著減少子查詢的執(zhí)行時間。

• 簡化子查詢：盡量簡化子查詢，使其只返回需要的最小數(shù)據(jù)量。例如，使用 SELECT 1 而不是 SELECT *。

• 避免計算：避免在子查詢中進行復(fù)雜的計算，可以在外部查詢中處理這些計算。

3. 兩者區(qū)別

(1) 用法：

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

(2) 功能：

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

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

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

(4) 性能:

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

(5) 空值處理：

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

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

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

總結(jié)

到此這篇關(guān)于MySQL關(guān)鍵字IN與EXISTS的使用與區(qū)別的文章就介紹到這了,更多相關(guān)MySQL關(guān)鍵字IN與EXISTS使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！