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这个项目没有任何成员。