sql - 三表连接与 INNER JOIN 以外的连接

Question

我正在学习 SQL 并在这周尝试学习 JOIN。

我已经达到可以进行三个表连接的水平，类似于我见过的许多示例。我仍在努力弄清楚事情如何运作的微小细节。我见过的所有三表连接示例都只使用 INNER JOINS。 LEFT 和 RIGHT JOIN 怎么样？你曾经在三个表连接中使用过这些吗？这意味着什么？

SELECT ~some columns~ FROM ~table name~
LEFT JOIN ~table 2~ ON ~criteria~
INNER JOIN ~table 3~ ON ~criteria~

或

SELECT ~some columns~ FROM ~table name~
INNER JOIN ~table 2~ ON ~criteria~
LEFT JOIN ~table 3~ ON ~criteria~

或

SELECT ~some columns~ FROM ~table name~
LEFT JOIN ~table 2~ ON ~criteria~
LEFT JOIN ~table 3~ ON ~criteria~

或

???

尽可能多地探索空间

Answer 1

是的，我确实使用了所有这三个 JOIN，尽管我倾向于坚持只使用 LEFT (OUTER) JOIN 而不是混合使用 LEFT 和 RIGHT JOIN。我还使用 FULL OUTER JOIN 和 CROSS JOIN。

总而言之，INNER JOIN 将结果集限制为仅满足 JOIN 条件的那些记录。考虑下表

编辑:我重命名了表格名称并在它们前面加上 @ 以便任何阅读此答案并想要试验的人都可以使用表格变量。

如果您还想在浏览器中进行试验， I've set this all up on SQL Fiddle 也是；

@Table1

id | name
---------
1  | One
2  | Two
3  | Three
4  | Four

@Table2

id | name
---------
1  | Partridge
2  | Turtle Doves
3  | French Hens
5  | Gold Rings

SQL 代码

DECLARE @Table1 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table1 VALUES(1, 'One');
INSERT INTO @Table1 VALUES(2, 'Two');
INSERT INTO @Table1 VALUES(3, 'Three');
INSERT INTO @Table1 VALUES(4, 'Four');

DECLARE @Table2 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table2 VALUES(1, 'Partridge');
INSERT INTO @Table2 VALUES(2, 'Turtle Doves');
INSERT INTO @Table2 VALUES(3, 'French Hens');
INSERT INTO @Table2 VALUES(5, 'Gold Rings');

INNER JOIN SQL 语句，在 id 字段上加入

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

结果在

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens

LEFT JOIN 将返回一个结果集，其中包含连接左侧表中的所有记录(如果您将语句写成一行，则首先出现的表) 以及连接右侧表中与连接表达式匹配并包含在 SELECT 子句中的字段。 缺少 详细信息将填充为 NULL

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
LEFT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

结果

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
4  | Four | NULL

RIGHT JOIN 与 LEFT JOIN 的逻辑相同，但会返回连接右侧的所有记录和左侧匹配的字段连接表达式并包含在 SELECT 子句中。

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
RIGHT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

结果

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
NULL| NULL| Gold Rings

当然，还有 FULL OUTER JOIN，它包括来自两个连接表的记录，并用 NULL 填充任何缺失的详细信息。

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
FULL OUTER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

结果

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
4  | Four | NULL
NULL| NULL| Gold Rings

还有一个CROSS JOIN(也称为CARTESIAN PRODUCT)，它只是SELECT语句中交叉应用字段的产物从一个表中获取另一表中 SELECT 语句中的字段。请注意，CROSS JOIN

中没有连接表达式

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
CROSS JOIN
    @Table2 t2

结果在

id | name  | name
------------------
1  | One   | Partridge
2  | Two   | Partridge
3  | Three | Partridge
4  | Four  | Partridge
1  | One   | Turtle Doves
2  | Two   | Turtle Doves
3  | Three | Turtle Doves
4  | Four  | Turtle Doves
1  | One   | French Hens
2  | Two   | French Hens
3  | Three | French Hens
4  | Four  | French Hens
1  | One   | Gold Rings
2  | Two   | Gold Rings
3  | Three | Gold Rings
4  | Four  | Gold Rings

编辑:

假设现在有一个 Table3

@Table3

id | name
---------
2  | Prime 1
3  | Prime 2
5  | Prime 3

SQL 代码

DECLARE @Table3 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table3 VALUES(2, 'Prime 1');
INSERT INTO @Table3 VALUES(3, 'Prime 2');
INSERT INTO @Table3 VALUES(5, 'Prime 3');

现在所有三个表都通过 INNER JOINS 连接

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
INNER JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

结果

id | name | name         | name
-------------------------------
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2

考虑到 id 为 2 和 3 的记录是所有 3 个表中唯一共有的记录，这可能有助于理解这个结果并且也是我们加入每个表的字段。

现在所有三个都带有 LEFT JOINS

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
LEFT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
LEFT JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

结果

id | name | name         | name
-------------------------------
1  | One  | Partridge    | NULL
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2
4  | Four | NULL         | NULL

Joel's answer是解释这个结果集的一个很好的解释(表 1 是基/源表)。

现在有一个 INNER JOIN 和一个 LEFT JOIN

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
LEFT JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

结果

id | name | name         | name
-------------------------------
1  | One  | Partridge    | NULL
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2

虽然我们不知道查询优化器执行操作的顺序，但我们将从上到下查看此查询以了解结果集。表 1 和表 2 之间的 ID 上的 INNER JOIN 会将结果集限制为仅满足连接条件的记录，即我们在第一个示例中看到的三行。这个临时结果集将LEFT JOIN在Table1和Tables之间的id上编辑到Table3； Table3 中有 id 为 2 和 3 的记录，但没有 id 1，因此 t3.name 字段将包含 2 和 3 而不是 1 的详细信息。