使用 case 语句的 Oracle SQL 条件外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15755120/
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
Oracle SQL Conditional Outer Join using case statement
提问by Emmanuel
Lets say that I have three tables:
假设我有三个表：
1. TableA with columns TableAID (PK), Desc Nullable
2. TableB with columns TableBID (PK), TableAID(FK) Nullable
3. TableC with columns TableCID (PK), TableBID (FK) Nullable,
TableAID (FK) Nullable, Start_Date, End_Date
I need to return the Desc in Table A if TableC.TableBID is not null then use the TableAID(FK) in TableB to retrieve Desc else use TableAID (FK) in TableC to retrieve Desc
如果 TableC.TableBID 不为空，我需要返回表 A 中的 Desc 然后使用 TableB 中的 TableAID(FK) 来检索 Desc 否则使用 TableC 中的 TableAID (FK) 来检索 Desc
Note: It's possible both TableC.TableBID or TableC.TableAID can be null. In all cases I still must be able to return the other columns in TableC.
注意：TableC.TableBID 或 TableC.TableAID 都可能为空。在所有情况下，我仍然必须能够返回 TableC 中的其他列。
Here is my code:
这是我的代码：
Select ta.desc, tc.start_date, tc.end_date
from TableC tc
Left outer join TableB tb
on case
when tc.TableBID is not null then (
tc.TableBID = tb.TableBID
Left outer join TableA ta
on tb.TableAID = ta.TableAID
--my concern here is that tb.TableAID can be null. Will it still work?
)
else tc.TableAID = ta.TableAID --my concern here is that tc.TableAID can be null.
--WIll it still work?
I'm also concern about syntax. If there is a better way to have a conditional join, please advise. I'm using oracle. This code will go into a view which will be used for a search procedure (that's why it has to return everything regardless of nulls). Thanks for your help.
我也关心语法。如果有条件加入的更好方法，请指教。我正在使用甲骨文。此代码将进入将用于搜索过程的视图（这就是为什么它必须返回所有内容而不管空值）。谢谢你的帮助。
采纳答案by Rachcha
You can put your CASEin the SELECTclause and join the tables accordingly, like this:
你可以把你的CASE放在SELECT子句中并相应地加入表格，如下所示：
SELECT CASE
WHEN tb.tableAID IS NOTNULL THEN tab.desc
ELSE tac.desc
END AS desc
-- or better: NVL(tab.desc, tac.desc) AS desc
, tc.start_date
, tc.end_date
FROM tableC tc
JOIN tableB tb ON tc.tableBID = tb.tableBID
LEFT JOIN tableA tab ON tab.tableAID = tb.tableAID
LEFT JOIN tableA tac ON tac.tableAID = tc.tableAID
