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;

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

下圖說明瞭上述操作的結果：

在本教學中，學習了各種SQL Server連接，這些連接組合了兩個表中的數據。