SQL Server FULL OUTER JOIN子句

在本教學中，將學習如何使用SQL Server FULL OUTER JOIN(全外連接)來查詢來自兩個或多個表的數據。

SQL Server全外連接簡介

FULL OUTER JOIN返回一個包括左右表中行記錄的結果集。 如果左表中的行不存在匹配的行，則右表的列將具有NULL值。 相反，如果右表中的行不存在匹配的行，則左表的列將具有NULL值。

下麵顯示了連接兩個表時FULL OUTER JOIN的語法：

SELECT
    select_list
FROM
    T1
FULL OUTER JOIN T2 ON join_predicate;

OUTER關鍵字是可選的，因此可以不用寫上它，如以下查詢中所示：

SELECT
    select_list
FROM
    T1
FULL JOIN T2 ON join_predicate;

在這個語法中：

• 在FROM子句中指定左表T1。
• 指定右表T2和連接謂詞。

下圖說明瞭FULL OUTER JOIN的兩個結果集：

SQL Server完全外連接示例

下麵創建一些示例表來演示全外連接。

首先，創建一個名為pm的新模式，它代表專案管理。

CREATE SCHEMA pm;
GO

接下來，在pm模式中創建名為projects和members的新表：

CREATE TABLE pm.projects(
    id INT PRIMARY KEY IDENTITY,
    title VARCHAR(255) NOT NULL
);

CREATE TABLE pm.members(
    id INT PRIMARY KEY IDENTITY,
    name VARCHAR(120) NOT NULL,
    project_id INT,
    FOREIGN KEY (project_id)
        REFERENCES pm.projects(id)
);

假設每個成員只能參與一個專案，每個專案都有零個或多個成員。 如果專案處於構思階段，則不會分配任何成員。

然後，向projects和member表中插入一些行記錄：

INSERT INTO
    pm.projects(title)
VALUES
    ('New CRM for Project Sales'),
    ('ERP Implementation'),
    ('Develop Mobile Sales Platform');


INSERT INTO
    pm.members(name, project_id)
VALUES
    ('John Doe', 1),
    ('Lily Bush', 1),
    ('Jane Doe', 2),
    ('Jack Daniel', null);

之後，查詢projects和member表中的數據：

SELECT * FROM pm.projects;
SELECT * FROM pm.members;

最後，使用FULL OUTER JOIN查詢projects和member表中的數據：

SELECT
    m.name member,
    p.title project
FROM
    pm.members m
    FULL OUTER JOIN pm.projects p
        ON p.id = m.project_id;

執行上面查詢語句，得到以下結果：

在此示例中，查詢返回參與專案的成員，不參與任何專案的成員以及沒有任何成員的專案。

要查找不參與任何專案的成員和沒有任何成員的專案，請在上述查詢中添加WHERE子句：

SELECT
    m.name member,
    p.title project
FROM
    pm.members m
    FULL OUTER JOIN pm.projects p
        ON p.id = m.project_id
WHERE
    m.id IS NULL OR
    P.id IS NULL;

執行上面查詢語句，得到以下結果：

如輸出中清楚顯示，Jack Daniel不參與任何專案，而Develop Mobile Sales Platform這個專案沒有任何成員。