在 SQL 查詢中，LEFT JOIN 是一種常用的連接類型，用于從左表（主表）中返回所有記錄，以及右表（連接表）中滿足連接條件的記錄。當(dāng) LEFT JOIN 與 ON 和 WHERE 子句結(jié)合使用時(shí)，其行為和結(jié)果可能會有顯著不同。本文將深入探討這種差異，并通過實(shí)例代碼進(jìn)行詳細(xì)解析。

1. LEFT JOIN 與 ON

ON 子句用于指定 LEFT JOIN 的連接條件。當(dāng)使用 ON 子句時(shí)，即使在右表中沒有匹配的記錄，左表的所有記錄也會被返回，右表的字段在這種情況下會填充 NULL。

示例代碼

假設(shè)我們有兩個(gè)表：employees（員工表）和 departments（部門表）。

-- 創(chuàng)建示例表
CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    department_id INT
);

CREATE TABLE departments (
    id INT,
    department_name VARCHAR(100)
);

-- 插入示例數(shù)據(jù)
INSERT INTO employees (id, name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', NULL);

INSERT INTO departments (id, department_name) VALUES
(1, 'HR'),
(2, 'Engineering');

現(xiàn)在我們想要查詢所有員工及其對應(yīng)的部門名稱，即使某些員工沒有分配部門。

-- 使用 LEFT JOIN 和 ON 子句
SELECT 
    e.id,
    e.name,
    d.department_name
FROM 
    employees e
LEFT JOIN 
    departments d
ON 
    e.department_id = d.id;

輸出結(jié)果：

2. LEFT JOIN 與 WHERE

WHERE 子句用于過濾查詢結(jié)果。當(dāng)在 LEFT JOIN 后使用 WHERE 子句時(shí)，它會過濾掉所有不滿足條件的記錄，包括因?yàn)?LEFT JOIN 而產(chǎn)生的 NULL 值。

示例代碼

如果我們錯(cuò)誤地將連接條件放在 WHERE 子句中：

-- 使用 LEFT JOIN 和錯(cuò)誤的 WHERE 子句
SELECT 
    e.id,
    e.name,
    d.department_name
FROM 
    employees e
LEFT JOIN 
    departments d
ON 
    1=1  -- 這里故意使用一個(gè)總是為真的條件
WHERE 
    e.department_id = d.id;

輸出結(jié)果：

注意到 Charlie 被排除了，因?yàn)?WHERE 子句過濾掉了 department_id 不匹配的記錄，包括那些因?yàn)?LEFT JOIN 本應(yīng)返回 NULL 的記錄。

3. 正確使用 ON 和 WHERE

場景一：過濾連接后的結(jié)果

如果我們想要查詢所有員工及其部門名稱，但只顯示那些部門名稱不為空的記錄，可以這樣做：

-- 使用 LEFT JOIN 和正確的 WHERE 子句
SELECT 
    e.id,
    e.name,
    d.department_name
FROM 
    employees e
LEFT JOIN 
    departments d
ON 
    e.department_id = d.id
WHERE 
    d.department_name IS NOT NULL;

輸出結(jié)果：

場景二：額外的過濾條件

如果我們想要查詢所有在“Engineering”部門的員工，同時(shí)保留其他員工的記錄但部門名稱為空，可以這樣做：

-- 使用 LEFT JOIN、ON 和額外的過濾條件
SELECT 
    e.id,
    e.name,
    d.department_name
FROM 
    employees e
LEFT JOIN 
    departments d
ON 
    e.department_id = d.id
AND 
    d.department_name = 'Engineering';  -- 這個(gè)條件放在 ON 中

輸出結(jié)果：

注意到 Alice 和 Charlie 仍然保留在結(jié)果中，但他們的部門名稱顯示為 NULL，因?yàn)樗麄儾粚儆凇癊ngineering”部門。

結(jié)論

在 LEFT JOIN 中，ON 和 WHERE 子句的使用有顯著區(qū)別。ON 子句用于指定連接條件，而 WHERE 子句用于過濾結(jié)果。將連接條件錯(cuò)誤地放在 WHERE 子句中會導(dǎo)致 LEFT JOIN 的行為類似于 INNER JOIN，從而失去左表中的一些記錄。正確理解和使用 ON 和 WHERE 子句對于獲得預(yù)期的查詢結(jié)果至關(guān)重要。