7.7 SQL Server连接查询

SQL Server 连接查询

目录

• SQL Server 连接查询
  • 简介
  • 创建示例表
  • SQL Server内连接
  • SQL Server左联接
  • SQL Server右连接
  • SQL Server全联接

简介

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

先创建示例表进行演示。

创建示例表

首先，创建一个名为hr的新schema（架构）：

CREATE SCHEMA hr;
GO

然后在hrschema中创建两张表分别是candidates(候选人)、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
);

两张表分别插入几行数据：

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

我们将候选人表称为左表，将员工表称为右表。

SQL Server内连接

内联接生成一个数据集，其中包括左表中的行，与右表中相匹配的行

下面示例连接candidates和employees表，通过条件candidates.fullname=employees.fullname
也就是从candidates表中获取行，这些行的fullname与employees表的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;

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

SQL Server左联接

左联接选择数据从左表开始，并在右表中匹配行。左联接返回左表中的所有行和右表中的匹配行。如果左表中的一行与右表中的行不匹配，则右表的列将为空。

左联(Left Join)接也称为左外联接(Left Outer Join)。Outer关键字可省略。

下面的语句使用左联接将候选人表与员工表联接起来：

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;

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

要获取仅在左表中可用但在右表中不可用的行(即左表中与右表没匹配上的行)，可以在上面的查询中添加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;

下面的Venn图说明了左联接的结果，该联接选择仅在左表中可用的行：

SQL Server右连接

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

右联接返回一个结果集，其中包含右表中的所有行和左表中的匹配行。如果右表中的行在左表中没有匹配的行，则左表中的所有列都将为Null。

使用右联接查询候选人表与员工表：

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）中的所有行都包含在结果集中。

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

类似地，通过向上述查询添加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;

Venn图：

SQL Server全联接

全外联接或全联接返回一个结果集，该结果集包含左表和右表中的所有行，以及两侧的匹配行（如果可用）。如果不匹配，则缺少的一侧将为空值。

在候选人表和员工表之间做全联接查询：

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;

全（外）联接的Venn图：

要选择左表或右表中存在的行，可以通过添加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;

Venn图：