与INNER JOIN以外的连接的三表连接 [英] Three table join with joins other than INNER JOIN
问题描述
我正在学习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 column〜FROM〜table name〜
INNER JOIN〜table 2〜ON〜criteria〜
LEFT JOIN〜table 3〜 ON〜条件〜
或
SELECT〜some columns〜FROM〜table name〜
LEFT JOIN〜table 2〜ON〜criteria〜
LEFT JOIN〜table 3〜ON〜criteria〜
或
只是试图尽可能地探索这个空间。
三个JOIN，虽然我倾向于使用 LEFT（OUTER）JOIN s，而不是混合LEFT和RIGHT JOIN。我还使用 FULL OUTER JOIN s和 CROSS JOIN 。
总之， INNER JOIN 仅将结果集限制为JOIN条件满足的记录。请考虑下列表格：
EDIT：我已重命名表名称并以 @ ，以便表变量可以用于任何阅读这个答案和想要实验的人。
如果你还想在浏览器中试验这个， strong> 我已将此设置为SQL Fiddle strong>太;
@ Table1
id | name
---------
1 |一个
2 |两个
3 |三
4 |四
@ Table2
id | name
---------
1 | Partridge
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，'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
----------------
1 |一| Partridge
2 |二|乌龟鸠
3 |三| French Hens
A LEFT JOIN 结果集与来自连接左侧表中的所有记录（如果您要将语句写为一个线索，首先出现的表）和来自连接右侧的表中与字段匹配的字段连接表达式并包含在 SELECT 子句中。 缺少的详细信息将用NULL填充
SELECT
t1.id，
t1.name，
t2.name
FROM
@表1 t1
LEFT JOIN
@ Table2 t2
ON
t1.id = t2.id
结果
id |名称| name
----------------
1 |一| Partridge
2 |二|乌龟鸠
3 |三|法语母鸡
4 |四| NULL
A 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
----------------
1 |一| Partridge
2 |二|乌龟鸠
3 |三|法语母语
NULL | NULL |金戒指
当然，还有 FULL OUTER JOIN ，其中包括来自两个连接表格的记录，并填充任何缺少的细节为NULL。
SELECT
t1.id，
t1.name，
t2.name
FROM
@ Table1 t1
FULL OUTER JOIN
@ Table2 t2
ON
t1.id = t2.id
p>
id |名称| name
----------------
1 |一| Partridge
2 |二|乌龟鸠
3 |三|法语母鸡
4 |四| NULL
NULL | NULL |金戒指
和 CROSS JOIN 称为 CARTESIAN PRODUCT ），这只是一个表中 SELECT 语句中交叉应用字段的产物来自其他表的 SELECT 语句中的字段。请注意，在 CROSS JOIN
SELECT
t1.id，
t1.name，
t2.name
FROM
@表1 t1
CROSS JOIN
@ Table2 t2
结果
id |名称| name
------------------
1 |一| Partridge
2 |二| Partridge
3 |三| Partridge
4 |四| Partridge
1 |一|龟鸠
2 |二|乌龟鸠
3 |三|龟鸠
4 |四|乌龟鸠
1 |一|法语母鸡
2 |二|法语母鸡
3 |三|法语母鸡
4 |四|法语母鸡
1 |一|金戒指
2 |二|金戒指
3 |三|金戒指
4 |四|金戒指
编辑：
想象一下现在有一个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
-------------------------------
2 |二|乌龟鸽子Prime 1
3 |三|法语Hens | Prime 2
这可能有助于理解这个结果，认为id 2和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
结果
名称|名称| name
-------------------------------
1 |一| Partridge | NULL
2 |二|乌龟鸽子Prime 1
3 |三|法语Hens | Prime 2
4 |四| NULL | NULL
Joel的回答是解释此结果集的一个很好的解释（Table1是基表/原始表）。
现在有 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
结果in
id |名称|名称| name
-------------------------------
1 |一| Partridge | NULL
2 |二|乌龟鸽子Prime 1
3 |三|法语Hens | Prime 2
虽然我们不知道查询优化器执行操作的顺序，看看这个查询从上到下了解结果集。在Table1和Table2之间的id上的 INNER JOIN 会将结果集限制为仅由连接条件满足的那些记录，即我们在第一个示例中看到的三行。然后，这个临时结果集将 LEFT JOIN 编辑到Table1和Table1之间的ID;在表3中有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.
这篇关于与INNER JOIN以外的连接的三表连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
