7.10 SQL Server全外连接查询

SQL Server Full Outer Join

目录

• SQL Server Full Outer Join
  • SQL Server全外连接简介
  • 全连接示例

SQL Server全外连接简介

全外连接也称全连接，效果很像左连接和右连接的结合起来。

当左表中的行不存在匹配行时，右表的列将包含NULL。同样，当右表中的行不存在匹配行时，左表的列将包含NULL。

语法：

SELECT 
    select_list
FROM 
    T1
FULL OUTER JOIN T2 ON condition;

同样OUTER关键字可省略：

SELECT 
    select_list
FROM 
    T1
FULL JOIN T2 ON condition;

语法中：

• 首先在FROM子句中指定左表T1。
• 然后指定右表T2和连接条件。

下面的维恩图解说明了FULL OUTER JOIN的结果：

全连接示例

先给示例创建几张新表：

首先，创建一个名为pm(project management)的架构，代表项目管理：

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)
);

假设每个成员只能参与一个项目，并且每个项目有零个或多个成员。如果项目处于初始阶段，则没有分配成员。

然后，在项目和成员表中插入一些行：

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);

然后，查看一下两个表中的数据

SELECT * FROM pm.projects;

SELECT * FROM pm.members;

最后，使用FULL OUTER JOIN查询项目和成员表中的数据：

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

在本例中，查询返回参与项目的成员、不参与任何项目的成员以及没有任何成员的项目。

要查找不参与任何项目的成员和没有任何成员的项目，请在上述查询SQL语句中添加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没有任何成员。