SQL 如何从两个表到第三个表的左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15140933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to left outer join from two tables to a third table
提问by stu
I've got three tables A B and C (the last is the result I want)
我有三个表 AB 和 C（最后一个是我想要的结果）
A.id B.age C.id C.age result id age
1 5 1 5 1 5
2 6 2 0 null null
3 7 0 7 null null
4 8 4 8 4 8
5 9 5 9 5 9
I want to do an outer join from A and B to C such that I end up with result above. If either of the columns are missing from C, it should yield null. If I do an inner join:
我想做一个从 A 和 B 到 C 的外部连接，这样我就得到了上面的结果。如果 C 中缺少任何一列，则应生成 null。如果我进行内部联接：
select a.id, b.age where a.id = c.id and b.age = c.age
I'll get 3 rows back. I still want 5 rows back. I'm using IBM db2 v9 something. I'm trying to figure out using the newer left outer join syntax how to make it go, but I'm not seeing it. I can see how I'd do it in the old sybase *= outer join syntax, but I can't see how to do it the new style way. Is this even possible?
我会拿回 3 行。我还想要 5 行。我正在使用 IBM db2 v9 的东西。我试图找出使用较新的左外连接语法如何使它运行，但我没有看到它。我可以看到如何在旧的 sybase *= 外连接语法中执行此操作，但我看不到如何以新样式方式执行此操作。这甚至可能吗？
Somebody suggested doing a union of half the results of each. Unions just make a mess, seems to me, I should be able to do a left outer join from both tables.
有人建议将每个结果的一半合并。工会只是一团糟，在我看来，我应该能够从两个表中进行左外连接。
Help? Thanks.
帮助？谢谢。
回答by devio
Do you mean this?
你是这个意思吗？
SELECT a.id, b.age
FROM a
CROSS JOIN b
LEFT OUTER JOIN c on a.id = c.id AND b.age = c.age
Your question is not clear on whether you cross join A and B or whether B is also left joined:
您的问题不清楚是交叉加入 A 和 B 还是 B 也加入：
SELECT a.id, b.age
FROM a
LEFT OUTER JOIN c on a.id = c.id
LEFT OUTER JOIN b on b.age = c.age
There are even more possible combinations...
还有更多可能的组合......
回答by Joseph Wright
-- A Dummy table
with A as (
select
2 as id
from
sysibm.sysdummy1)
-- B Dummy table
, B as (
select
6 as age
from
sysibm.sysdummy1)
-- C Dummy table
, C as (
select
2 as id,
0 as age
from
sysibm.sysdummy1)
-- Actual result query
select
A.id as "A.id",
B.age as "B.age",
C.id as "C.id",
C.age as "C.age",
case
when A.id = C.id and B.age = C.age then
C.id
else
null
end as "result id",
case
when A.id = C.id and B.age = C.age then
C.age
else
null
end as "result age"
from
sysibm.sysdummy1 as Dummy
left outer join A as A on 1=1
left outer join B as B on 1=1
left outer join C as C on 1=1
Here's my solution that will produce matching results to the original post. Just swap out the values in A, B, and C dummy tables with the examples provided.
这是我的解决方案，它将产生与原始帖子匹配的结果。只需用提供的示例交换 A、B 和 C 虚拟表中的值。
Edit:The same result without using a case statement:
编辑：不使用 case 语句的相同结果：
select
A.id as "A.id",
B.age as "B.age",
C.id as "C.id",
C.age as "C.age",
R.id as "result id",
R.age as "result age"
from
sysibm.sysdummy1 as Dummy
left outer join A as A on 1=1
left outer join B as B on 1=1
left outer join C as C on 1=1
left outer join C as R
on A.id = R.id
and B.age = R.age
回答by Sunil
It's a late response to this question but the query below should do it.
这是对这个问题的迟到回应，但下面的查询应该这样做。
I have created an online sqlfiddle at http://sqlfiddle.com/#!3/07d74/5
我在http://sqlfiddle.com/#!3/07d74/5创建了一个在线 sqlfiddle
You can verify your requirements using above online sqlfiddle. It gives the exact same results that you asked for.
您可以使用上面的在线 sqlfiddle 验证您的要求。它提供了与您要求的完全相同的结果。
I could not find an online tool for running DB2 queries even after extensive googling, but I checked IBM's docs on DB2 syntax for cross join and left join before coming up with this query. So, it should work in DB2, but please let me know if you see any issues. If you know an online tool for DB2 please let me know. The sqlfiddle is based on Sql Server since there is no option for DB2 on sqlfiddle, but like I said the query is compatible with DB2 as per the docs I read.
即使经过大量的谷歌搜索，我也找不到用于运行 DB2 查询的在线工具，但在提出此查询之前，我检查了 IBM 的有关 DB2 语法的文档，以了解交叉联接和左联接。所以，它应该可以在 DB2 中工作，但是如果您发现任何问题，请告诉我。如果您知道 DB2 的在线工具，请告诉我。sqlfiddle 基于 Sql Server，因为 sqlfiddle 上没有 DB2 选项，但就像我说的，根据我阅读的文档，查询与 DB2 兼容。
The DB2 compatible query is as below.
DB2 兼容查询如下。
SELECT D.id,
D.age
FROM C
LEFT OUTER JOIN
(SELECT A.id,
B.age
FROM A
CROSS JOIN B) D ON D.id = C.id
AND D.age = C.age;
回答by bwperrin
Sorry about incomplete and incorrect "FULL JOIN" post.
很抱歉不完整和不正确的“FULL JOIN”帖子。
Wouldn't it be:
会不会是：
select case when b.age is not null then a.id end as id
, case when a.id is not null then b.age end as age
from c
left join a on a.id = c.id
left join b on b.age = c.age
