SQL Server Join表

在本教程中，将了解各种SQL Server联接，它们用于组合来自两个表的数据。

在关系数据库中，数据分布在多个逻辑表中。要获得完整有意义的数据集，需要使用连接来查询这些表中的数据。 SQL Server支持多种连接，包括内连接，左连接，右连接，全外连接和交叉连接。每种连接类型指定SQL Server如何使用一个表中的数据来选择另一个表中的行。

为了方便演示，下面将创建一些示例表。

A. 创建示例表

首先，创建一个名为hr的新模式：

CREATE SCHEMA hr;
GO

其次，在hr模式中创建两个名为candidate和employees的新表：

CREATE TABLE hr.candidates(
    id INT PRIMARY KEY IDENTITY,
    fullname VARCHAR(100) NOT NULL
);

CREATE TABLE hr.employees(
    id INT PRIMARY KEY IDENTITY,
    fullname VARCHAR(100) NOT NULL
);

第三，在candidate和employees表中插入一些行：

INSERT INTO 
    hr.candidates(fullname)
VALUES
    ('John Doe'),
    ('Lily Bush'),
    ('Peter Drucker'),
    ('Jane Doe');


INSERT INTO 
    hr.employees(fullname)
VALUES
    ('John Doe'),
    ('Jane Doe'),
    ('Michael Scott'),
    ('Jack Sparrow');

下面将candidate表用作左表，将employees表用作右表。

B. SQL Server内联接

内联接生成一个数据集，其中包含左表中的行，这些行具有右表中的匹配行。

以下示例使用inner join子句从employees表中获取在candidates表的fullname列中具有相同的值的行记录：

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    INNER JOIN hr.employees e 
        ON e.fullname = c.fullname;

执行上面查询语句，得到以下结果：

下图说明了两个结果集的内联接的结果：

C. SQL Server左连接

左连接选择从左表开始的数据和右表中的匹配行。左连接返回左表中的所有行和右表中的匹配行。如果左表中的行在右表中没有匹配的行，则右表的列将具有空值。

左连接也称为左外连接。 outer关键字是可选的。

以下语句使用left join将employees表与employees表连接起来：

SELECT  
 c.id candidate_id,
 c.fullname candidate_name,
 e.id employee_id,
 e.fullname employee_name
FROM 
 hr.candidates c
 LEFT JOIN hr.employees e 
 ON e.fullname = c.fullname;

执行上面查询语句，得到以下结果：

以下图说明了两个结果集的左连接结果：

要获取仅在左表中可用但不在右表中可用的行，可以在上面的查询中添加WHERE子句：

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    LEFT JOIN hr.employees e 
        ON e.fullname = c.fullname
WHERE 
    e.id IS NULL;

执行上面查询语句，得到以下结果：

以下图说明左连接的结果，它选择仅在左表中可用的行：

D. SQL Server右连接

右连接或右外连接从右表开始选择数据。它是左连接的反转版本。

右连接返回一个结果集，该结果集包含右表中的所有行和左表中的匹配行。如果右表中的一行在左表中没有匹配的行，则左表中的所有列都将包含NULL值。

以下示例使用右连接查询candidates 和 employees表中的行：

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    RIGHT JOIN hr.employees e 
        ON e.fullname = c.fullname;

执行上面查询语句，得到以下结果：

请注意，右表(employees)中的所有行都包含在结果集中。

下图表说明了两个结果集的右连接：

类似地，可以通过向上面的查询添加WHERE子句来获取仅在右表中可用的行，如下所示：

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    RIGHT JOIN hr.employees e 
        ON e.fullname = c.fullname
WHERE
    c.id IS NULL;

执行上面查询语句，得到以下结果：

下图说明了查询操作的结果：

E. SQL Server全连接

完整外连接或完全连接返回一个结果集，该结果集包含左右表中的所有行，两侧的匹配行可用。如果没有匹配，则缺少的一方将具有NULL值。

以下示例显示如何在candidates 和 employees表之间执行完全联接：

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    FULL JOIN hr.employees e 
        ON e.fullname = c.fullname;

执行上面查询语句，得到以下结果：

下图说明了全连接：

要选择存在左表或右表的行，可以通过添加WHERE子句来排除两个表共有的行，如以下查询中所示：

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    FULL JOIN hr.employees e 
        ON e.fullname = c.fullname
WHERE
    c.id IS NULL OR
    e.id IS NULL;

执行上面查询语句，得到以下结果：