oracle 从左外连接中排除集合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1434621/
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
Exclude set from left outer join
提问by Dave Jarvis
How do you exclude a set of values when using a left outer join?
使用左外连接时如何排除一组值？
Consider the following query:
考虑以下查询：
SELECT i.id,
i.location,
area.description
FROM incident_vw i,
area_vw area
WHERE i.area_code = area.code(+)
AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
The query executes, yet none of the NULL area code values appear.
查询执行，但没有出现任何 NULL 区号值。
BE AWARE: INCIDENT_VW.area_codecan and does have NULL values.
请注意：INCIDENT_VW.area_code可以并且确实具有 NULL 值。
Any ideas on how to match NULL incident area codes while excluding the given set of area codes, without using PL/SQL?
关于如何在不使用 PL/SQL 的情况下在排除给定的区号集的同时匹配 NULL 事件区号的任何想法？
ANSI Update
ANSI 更新
Using the equivalent ANSI SQL also does not work:
使用等效的 ANSI SQL 也不起作用：
SELECT i.id,
i.location,
area.description
FROM incident_vw i
LEFT JOIN area_vw area
ON area.code = i.area_code
WHERE i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
Solution
解决方案
This works:
这有效：
SELECT i.id,
i.location,
area.description
FROM incident_vw i,
area_vw area
WHERE i.area_code = area.code(+)
AND (i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P') and i.area_code IS NULL)
Thanks everyone!
谢谢大家！
回答by Yannick Motton
Seems the problem is the IN is removing all the area_codes that are NULL.
似乎问题是 IN 正在删除所有为 NULL 的 area_codes。
Give this a try:
试试这个：
SELECT i.id,
i.location,
area.description
FROM incident_vw i
LEFT JOIN area_vw area
ON area.code = i.area_code
WHERE (i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
OR i.area_code IS NULL)
Should give the desired result...
应该给出想要的结果...
回答by Jeremy Bourque
I think I would try this:
我想我会试试这个：
SELECT i.id,
i.location,
area.description
FROM (SELECT *
FROM incident_vw
WHERE i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
OR i.area_code IS NULL) i
, area_vw area
WHERE i.area_code = area.code (+)
Or the ANSI equivalent:
或 ANSI 等价物：
SELECT i.id,
i.location,
area.description
FROM (SELECT *
FROM incident_vw
WHERE i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
OR i.area_code IS NULL) i
LEFT JOIN area_vw area
ON i.area_code = area.code
回答by bob
I'm not 100% sure but maybe you are joining the l.area_code on the area.code when you should be joining the area_code on the l.area.code.
我不是 100% 肯定，但也许您在应该加入 l.area.code 上的 area_code 时加入了 area.code 上的 l.area_code。
SELECT i.id,
i.location,
area.description
FROM incident_vw i,
area_vw area
WHERE 1=1
AND i.area_code = area.code(+)
AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
Should Be:
应该：
SELECT i.id,
i.location,
area.description
FROM incident_vw i,
area_vw area
WHERE 1=1
AND i.area_code(+) = area.code
AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
回答by Ruffles
Could you try:
你能不能试试：
SELECT i.id,
i.location,
area.description
FROM incident_vw i
LEFT JOIN area_vw area
ON area.code = i.area_code
WHERE NVL(i.area_code,'something') NOT IN ('T20', 'V20B', 'V20O', 'V20P')
回答by hgmnz
SELECT i.id,
i.location,
area.description
FROM incident_vw i
LEFT JOIN area_vw area
ON i.area_code = area.code
WHERE i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
UNION
SELECT i.id,
i.location,
NULL as description
FROM incident_vw i
WHERE i.area_code IS NULL
回答by akf
The syntax looks correct. You should test your data to ensure your sanity. Try this:
语法看起来正确。您应该测试您的数据以确保您的理智。尝试这个：
SELECT count(*)
FROM incident_vw i
WHERE i.area_code NOT IN
(SELECT a.area_code
FROM area_vw a);
This will tell you if you have any incidents that don't have an area represented in the area table.
这将告诉您是否有任何事件没有在区域表中表示的区域。
回答by van
I am not sure I understand the question, but if you would like to rather get NULLs for the area codes in the exclude list, then just move your condition from WHERE to the JOIN condition:
我不确定我是否理解这个问题，但是如果您希望排除列表中的区号为 NULL，那么只需将您的条件从 WHERE 移动到 JOIN 条件：
SELECT i.id,
i.location,
area.description
FROM incident_vw i
LEFT JOIN area_vw area
ON area.code = i.area_code
AND area.code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
