1. IN & NOT IN

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

1.1 基本使用

• ?? 語(yǔ)法一：

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)中。

• ?? 語(yǔ)法二：

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

1.2 工作原理

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

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

對(duì)于靜態(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í)行過(guò)程如下：

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

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

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

1.2.2 子查詢的 IN 語(yǔ)句

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

?? 示例：

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

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

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

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

?? 結(jié)果集緩存

當(dāng)使用 IN 子查詢時(shí)，MySQL會(huì)將子查詢的結(jié)果集緩存到內(nèi)存中以加快主查詢的執(zhí)行。對(duì)于非常大的結(jié)果集，這可能會(huì)導(dǎo)致內(nèi)存占用過(guò)多。在這種情況下，可以考慮使用臨時(shí)表或其他優(yōu)化方法來(lái)降低內(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è)查詢語(yǔ)句，返回一個(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ì)逐條掃描外表的每一行，并對(duì)每一行執(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í)行過(guò)程如下：

• 初始化：MySQL 初始化主查詢，開(kāi)始掃描 employees 表。
• 逐行掃描外表：對(duì) employees 表中的每一行，執(zhí)行以下步驟：
  • 讀取一行：讀取當(dāng)前行的 department_id。
  • 執(zhí)行子查詢：針對(duì)當(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 子查詢對(duì)于外表中的每一行都會(huì)執(zhí)行一次，這意味著子查詢的性能對(duì)于整個(gè)查詢的性能至關(guān)重要。

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

• 簡(jiǎn)化子查詢：盡量簡(jiǎn)化子查詢，使其只返回需要的最小數(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ú)的查詢語(yǔ)句（返回結(jié)果必須只有一個(gè)字段）。
• EXISTS 關(guān)鍵字的子查詢通常返回一個(gè)布爾值，表示子查詢是否返回了任何行。

(4) 性能:

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

(5) 空值處理：

IN和EXISTS對(duì)待空值的方式不同。

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

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

總結(jié)

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

最新評(píng)論