oracle 查询以检查记录是否存在且列是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10288239/
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
query to check if record exists and column null
提问by Raghul Raman
I have a table and i have to check a particular column in null or has value.
Lets say the column name is order_price.
我有一张表，我必须检查空值或值中的特定列。
假设列名是order_price.
If i just check where order_price is nullthen this also includes records that are not in the table too.
For example i have order_id = 1whose order_price is null and i have order_id = 2which does not exist in order table. So instead of a where condition to check whether order_price is null, i want to know whether the column is null or the record does not exist.
如果我只是检查，where order_price is null那么这也包括不在表中的记录。
例如，我order_id = 1的 order_price 为空，而我的order_id = 2订单表中不存在。因此，而不是检查 order_price 是否为空的 where 条件，我想知道列是否为空或记录不存在。
I'm doing outer join on this table so i cannot map the primary keys of another table. I am using Oracle.
我在这个表上做外连接，所以我不能映射另一个表的主键。我正在使用甲骨文。
Thanks
谢谢
回答by
Instead of a Join , you can use the "EXISTS / NOT EXISTS" keyword and a subquery.
您可以使用“EXISTS / NOT EXISTS”关键字和子查询来代替 Join 。
e.g.
例如
SELECT parent.*
FROM parent
WHERE EXISTS (SELECT 1
FROM child
WHERE child.id_parent = parent.id AND child.somecolumn IS NULL)
You can play with "exists / not exists" depending on whether you want or don't want the predicate to match.
您可以根据您是否希望谓词匹配来使用“存在/不存在”。
回答by Quassnoi
SELECT o.*,
od.order_price,
CASE
WHEN od.order_id IS NULL THEN
'Not exists'
WHEN od.order_price IS NULL THEN
'Exists, IS NULL'
ELSE
'Exists, IS NOT NULL'
END AS nullness
FROM orders o
LEFT JOIN
order_data od
ON od.order_id = o.id
