三表联接与除 INNER JOIN 以外的联接

2022-01-01 00:00:00 sql database sql-server-2005 tsql sql-server

我正在学习 SQL，这周我正在尝试学习 JOIN.

我已经达到了可以执行三个表连接的级别，这与我见过的许多示例类似.我仍在努力弄清楚事情如何运作的微小细节.我见过的所有三个表连接的例子都只使用 INNER JOINS.LEFT JOIN 和 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~

或

???

尝试尽可能多地探索空间

解决方案

是的，我确实使用了所有这三个 JOIN，尽管我倾向于只使用 LEFT (OUTER) JOIN 代替LEFT 和 RIGHT JOIN 的混合.我也使用 FULL OUTER JOINs 和 CROSS JOINs.

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

我已经重命名了表名称并以 @ 作为前缀，以便任何阅读此答案并想要进行实验的人都可以使用表变量.>

如果您还想在浏览器中对此进行试验，我也在 SQL Fiddle 上设置了这一切；

@Table1身份证 |名称---------1 |一2 |二3 |三4 |四@表2身份证 |名称---------1 |鹧鸪2 |斑鸠3 |法国母鸡5 |金戒指

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, '三');INSERT INTO @Table1 VALUES(4, 'Four');声明@Table2 表(id​​ INT PRIMARY KEY CLUSTERED，[name] VARCHAR(25))插入@Table2 VALUES(1, '鹧鸪');INSERT INTO @Table2 VALUES(2, 'Turtle Doves');INSERT INTO @Table2 VALUES(3, '法国母鸡');INSERT INTO @Table2 VALUES(5, '金戒指');

一个INNER JOIN SQL 语句，加入到id 字段

SELECTt1.id,t1.name,t2.name从@表1 t1内部联接@表2 t2在t1.id = t2.id

结果

id |姓名 |名称----------------1 |一 |鹧鸪2 |二 |斑鸠3 |三|法国母鸡

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

SELECTt1.id,t1.name,t2.name从@表1 t1左加入@表2 t2在t1.id = t2.id

结果

id |姓名 |名称----------------1 |一 |鹧鸪2 |二 |斑鸠3 |三|法国母鸡4 |四 |空值

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

SELECTt1.id,t1.name,t2.name从@表1 t1右加入@表2 t2在t1.id = t2.id

结果

id |姓名 |名称----------------1 |一 |鹧鸪2 |二 |斑鸠3 |三|法国母鸡空|空|金戒指

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

SELECTt1.id,t1.name,t2.name从@表1 t1全外连接@表2 t2在t1.id = t2.id

结果

id |姓名 |名称----------------1 |一 |鹧鸪2 |二 |斑鸠3 |三|法国母鸡4 |四 |空值空|空|金戒指

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

中没有连接表达式

SELECTt1.id,t1.name,t2.name从@表1 t1交叉连接@表2 t2

结果

id |姓名 |名称------------------1 |一 |鹧鸪2 |二 |鹧鸪3 |三 |鹧鸪4 |四 |鹧鸪1 |一 |斑鸠2 |二 |斑鸠3 |三 |斑鸠4 |四 |斑鸠1 |一 |法国母鸡2 |二 |法国母鸡3 |三 |法国母鸡4 |四 |法国母鸡1 |一 |金戒指2 |二 |金戒指3 |三 |金戒指4 |四 |金戒指

想象一下现在有一个 Table3 

@Table3身份证 |名称---------2 |素数 13 |总理 25 |总理 3

SQL 代码

DECLARE @Table3 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))INSERT INTO @Table3 VALUES(2, 'Prime 1');插入@Table3 VALUES(3, 'Prime 2');插入@Table3 VALUES(5, 'Prime 3');

现在所有三个表都用 INNER JOINS

连接

SELECTt1.id,t1.name,t2.name,t3.name从@表1 t1内部联接@表2 t2在t1.id = t2.id内部联接@表3 t3在t1.id = t3.id

结果

id |姓名 |姓名 |名称-------------------------------2 |二 |斑鸠|素数 13 |三|法国母鸡 |总理 2

通过认为 id 为 2 和 3 的记录是所有 3 个表唯一共有的记录，可能有助于理解此结果并且也是我们连接每个表的字段.

现在所有三个都带有 LEFT JOINS

SELECTt1.id,t1.name,t2.name,t3.name从@表1 t1左加入@表2 t2在t1.id = t2.id左加入@表3 t3在t1.id = t3.id

结果

id |姓名 |姓名 |名称-------------------------------1 |一 |鹧鸪 |空值2 |二 |斑鸠|素数 13 |三|法国母鸡 |总理 24 |四 |空 |空值

乔尔的回答是一个很好的解释来解释这个结果集(表 1 是基/源表).

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

SELECTt1.id,t1.name,t2.name,t3.name从@表1 t1内部联接@表2 t2在t1.id = t2.id左加入@表3 t3在t1.id = t3.id

结果

id |姓名 |姓名 |名称-------------------------------1 |一 |鹧鸪 |空值2 |二 |斑鸠|素数 13 |三|法国母鸡 |总理 2

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

I am learning SQL and am trying to learn JOINs this week.

I have gotten to the level where I can do three table joins, similar to a lot of examples I've seen. I'm still trying to figure out the tiny details of how things work. All the examples I've seen of three table joins use INNER JOINS only. What about LEFT and RIGHT JOINs? Do you ever use these in three table joins? What would it mean? 

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

or

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

or

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

or

???

Just trying to explore the space as much as possible

解决方案

Yes, I do use all three of those JOINs, although I tend to stick to using just LEFT (OUTER) JOINs instead of inter-mixing LEFT and RIGHT JOINs. I also use FULL OUTER JOINs and CROSS JOINs.

In summary, an INNER JOIN restricts the resultset only to those records satisfied by the JOIN condition. Consider the following tables

EDIT: I've renamed the Table names and prefix them with @ so that Table Variables can be used for anyone reading this answer and wanting to experiment.

If you'd also like to experiment with this in the browser, I've set this all up on SQL Fiddle too;

@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 code

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

An INNER JOIN SQL Statement, joined on the id field 

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

Results in 

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

A LEFT JOIN will return a resultset with all records from the table on the left hand side of the join (if you were to write out the statement as a one liner, the table that appears first) and fields from the table on the right side of the join that match the join expression and are included in the SELECT clause. Missing details will be populated with NULL

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

Results in

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

A RIGHT JOIN is the same logic as a LEFT JOIN but will return all records from the right-hand side of the join and fields from the left side that match the join expression and are included in the SELECT clause.

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

Results in

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

Of course, there is also the FULL OUTER JOIN, which includes records from both joined tables and populates any missing details with NULL.

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

Results in

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

And a CROSS JOIN (also known as a CARTESIAN PRODUCT), which is simply the product of cross applying fields in the SELECT statement from one table with the fields in the SELECT statement from the other table. Notice that there is no join expression in a CROSS JOIN

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

Results in 

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

EDIT:

Imagine there is now a Table3 

@Table3

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

The SQL code

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

Now all three tables joined with 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

Results in

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

It might help to understand this result by thinking that records with id 2 and 3 are the only ones common to all 3 tables and are also the field we are joining each table on.

Now all three with 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

Results in

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 is a good explanation for explaining this resultset (Table1 is the base/origin table).

Now with a INNER JOIN and a 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

Results in

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

Although we do not know the order in which the query optimiser will perform the operations, we will look at this query from top to bottom to understand the resultset. The INNER JOIN on ids between Table1 and Table2 will restrict the resultset to only those records satisfied by the join condition i.e. the three rows that we saw in the very first example. This temporary resultset will then be LEFT JOINed to Table3 on ids between Table1 and Tables; There are records in Table3 with id 2 and 3, but not id 1, so t3.name field will have details in for 2 and 3 but not 1.

相关文章