Oracle 左外连接，只想要空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25381422/
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 left outer join, only want the null values
提问by Paul Brindley
I'm working on a problem with two tables. Charge and ChargeHistory. I want to display a selection of columns from both tables where either the matching row in ChargeHistory has a different value and/or date from Charge or if there is no matching entry in ChargeHistory at all.
我正在处理两个表的问题。充电和充电历史。我想显示从两个表中选择的列，其中 ChargeHistory 中的匹配行与 Charge 具有不同的值和/或日期，或者 ChargeHistory 中根本没有匹配的条目。
I'm using a left outer join declared using the ansi standard and while it does show the rows correctly where there is a difference, it isn't showing the null entries.
我正在使用使用 ansi 标准声明的左外连接，虽然它确实在存在差异的地方正确显示了行，但它没有显示空条目。
I've read that there can sometimes be issues if you are using the WHERE clause as well as the ON clause. However when I try and put all the conditons in the ON clause the query takes too long > 15 minutes (so long I have just cancelled the runs).
我读过，如果您同时使用 WHERE 子句和 ON 子句，有时会出现问题。但是，当我尝试将所有条件放在 ON 子句中时，查询花费的时间太长 > 15 分钟（我刚刚取消了运行时间）。
To make things worse both tables use a three part compound key.
更糟糕的是，两个表都使用三部分复合键。
Does anyone have any ideas as to why the null values are being left out?
有没有人对为什么忽略空值有任何想法？
SELECT values...
FROM bcharge charge
LEFT OUTER JOIN chgHist history
ON charge.key1 = history.key1 AND charge.key2 = history.key2 AND charge.key3 = history.key3 AND charge.chargeType = history.chargeType
WHERE charge.chargeType = '2'
AND (charge.value <> history.value OR charge.date <> history.date)
ORDER BY key1, key2, key
采纳答案by Rene
You probably want to explicitly select the null values:
您可能想要明确选择空值：
SELECT values...
FROM bcharge charge
LEFT OUTER JOIN chgHist history
ON charge.key1 = history.key1 AND charge.key2 = history.key2 AND charge.key3 = history.key3 AND charge.chargeType = history.chargeType
WHERE charge.chargeType = '2'
AND ((charge.value <> history.value or history.value is null) OR (charge.date <> history.date or history.date is null))
ORDER BY key1, key2, key
回答by Gordon Linoff
You can explicitly look for a match in the where. I would recommend looking at one of the keys used for the join:
您可以在where. 我建议查看用于以下操作的键之一join：
SELECT . . .
FROM bcharge charge LEFT OUTER JOIN
chgHist history
ON charge.key1 = history.key1 AND charge.key2 = history.key2 AND
charge.key3 = history.key3 AND charge.chargeType = history.chargeType
WHERE charge.chargeType = '2' AND
(charge.value <> history.value OR charge.date <> history.date OR history.key1 is null)
ORDER BY key1, key2, key;
The expressions charge.value <> history.valuechange the left outer jointo an inner joinbecause NULLresults will be filtered out.
表达式charge.value <> history.value更改left outer join为 an，inner join因为NULL结果将被过滤掉。
回答by simsim
Have a look at this site, it will be very helpful for you, visual illustration of all the join statements with code samples
看看这个站点，它对你很有帮助，所有连接语句和代码示例的可视化说明
Quoted of the relevant info in the above link:
引用上面链接中的相关信息：
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
Sample output:
示例输出：
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
Left outer join
左外连接
produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null
从表 A 中产生一组完整的记录，在表 B 中有匹配的记录（如果可用）。如果没有匹配，右侧将包含空
回答by Used_By_Already
For any field from an outer joined table used in the where clauseyou must also permit an IS NULLoption for that same field, otherwise you negate the effect of the outer join and the result is the same as if you had used an inner join.
对于在 中使用的外部连接表中的任何字段，where clause您还必须允许IS NULL该相同字段的选项，否则您否定外部连接的效果，结果与使用内部连接的结果相同。
SELECT
*
FROM bcharge charge
LEFT OUTER JOIN chgHist history
ON charge.key1 = history.key1
AND charge.key2 = history.key2
AND charge.key3 = history.key3
AND charge.chargeType = history.chargeType
WHERE charge.chargeType = '2'
AND (
(charge.value <> history.value OR history.value IS NULL)
OR
(charge.date <> history.date OR history.date IS NULL)
)
ORDER BY
key1, key2, key3
Edit: Appears that this is the same query structure used by Rene above, so treat this one as in support of that please.
编辑：这似乎与上面 Rene 使用的查询结构相同，因此请将此视为支持该查询结构。
回答by shree.pat18
A WHEREclause filters the data returned by a join. Therefore when your inner table has null data for a particular column, the corresponding rows get filtered out based on your specified condition. That is why you should move that logic to the ONclause instead.
一个WHERE条款通过过滤返回的数据结合。因此，当您的内部表具有特定列的空数据时，根据您指定的条件过滤掉相应的行。这就是为什么您应该将该逻辑移到ON子句中的原因。
For the performance issues, you could consider adding indexes on the columns used for joining and filtering.
对于性能问题，您可以考虑在用于连接和过滤的列上添加索引。
