在 SQL 中的 LEFT OUTER JOIN 中使用 CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2490192/
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
Using CASE Statements in LEFT OUTER JOIN in SQL
提问by s khan
I've got a scenario where I want to switch on two different tables in an outer join. It goes something like this:-
我有一个场景，我想在外连接中切换两个不同的表。它是这样的：-
select mytable.id,
yourtable.id
from mytable
left outer join (case
when mytable.id = 2 then table2
yourtable on table1.id = table2.id
else
table3 yourtable on table1.id = table3.id
end)
...but it doesn't work. Any suggestions?
......但它不起作用。有什么建议？
采纳答案by OMG Ponies
Use (Oracle 9i+):
使用（Oracle 9i+）：
SELECT mt.id,
COALESCE(yt1.id, yt2.id)
FROM MYTABLE mt
LEFT JOIN YOURTABLE yt1 ON yt1.id = mt.id
AND yt.id = 2
LEFT JOIN YOURTABLE yt2 ON yt2.id = mt.id
回答by Gabe
Here's another possibility, although I haven't tried it on Oracle:
这是另一种可能性，虽然我还没有在 Oracle 上尝试过：
select mytable.id,
yourtable.id
from table1 as mytable left outer join
(SELECT 2 AS tableid, *
FROM table2
UNION ALL
SELECT 1, *
FROM table3) as yourtable
ON mytable.id = yourtable.id
AND tableid = CASE WHEN mytable.id = 2 THEN 2 ELSE 1 END
回答by APC
This query joins records from the EMP table to either the DEPT table or the SPECIAL_OPS table, depending on the value of EMP.DEPTNO ...
此查询将 EMP 表中的记录连接到 DEPT 表或 SPECIAL_OPS 表，具体取决于 EMP.DEPTNO ...
SQL> select e.ename
2 , e.job
3 , e.deptno
4 , coalesce(d.dname, s.dname) as dname
5 from emp e
6 left outer join dept d
7 on ( e.deptno = 30
8 and e.deptno = d.deptno )
9 left outer join special_ops s
10 on ( e.deptno != 30
11 and e.deptno = s.deptno )
12 where e.deptno in (30,50)
13 order by e.deptno, e.empno
14 /
ENAME JOB DEPTNO DNAME
---------- --------- ---------- --------------
VAN WIJK SALESMAN 30 SALES
PADFIELD SALESMAN 30 SALES
BILLINGTON SALESMAN 30 SALES
SPENCER MANAGER 30 SALES
CAVE SALESMAN 30 SALES
HALL CLERK 30 SALES
VERREYNNE PLUMBER 50 SKUNKWORKS
FEUERSTEIN PLUMBER 50 SKUNKWORKS
8 rows selected.
SQL>
I have included the filter on EMP.DEPTNO in the ON clauses. This might be unnecessary if the data in the tables is exclusive (i.e. DEPTNO = 30 could only join to DEPT and DEPTNO = 50 could only join to SPECIAL_OPS). However, if the identifier can appear in both tables it is as well to be explicit. Besides, making our intent clear is always good practice. Apart from anything else, we cannot be sure about the future state of the data.
我在 ON 子句中包含了 EMP.DEPTNO 上的过滤器。如果表中的数据是独占的（即 DEPTNO = 30 只能连接到 DEPT，而 DEPTNO = 50 只能连接到 SPECIAL_OPS），这可能是不必要的。但是，如果标识符可以出现在两个表中，那么它也是显式的。此外，明确我们的意图总是很好的做法。除此之外，我们无法确定数据的未来状态。
