是否存在与SQL NULL相反的概念? [英] Is there a concept which is the 'opposite' of SQL NULL?
问题描述
有没有一个概念(带有实现-在Oracle SQL中供初学者使用)，其行为类似于通用"匹配器?
我的意思是；我知道NULL不等于任何值-包括NULL. 这就是为什么在SQL表达式中必须小心"IS NULL"而不是"= NULL"的原因.
我还知道使用NVL(在Oracle中)功能来检测NULL并将其替换为输出中的内容很有用.
但是:使用NVL替换NULL的内容必须与基础列的数据类型匹配；否则，您(正确)会得到一个错误.
一个例子:
我有一个表，其中的NULLABLE列名称"为VARCHAR2类型；并且其中包含一个NULL行.
我可以提取NULL并将其替换为NVL，如下所示:
SELECT NVL(name, 'NullyMcNullFace’) from my_table;
太好了.
但是，如果该列恰好是NUMBER(例如年龄")，那么我必须更改我的NVL:
SELECT NVL(age, 32) from my_table;
也很棒.
现在，如果该列恰好是DATE(例如"somedate")，那么我必须再次更改我的NVL:
SELECT NVL(somedate, sysdate) from my_table;
我在这里得到的是:为了处理NULL，您必须替换为特定的 something ；并且特定的某物必须适合"数据类型.
这里是否有一个像"ANY"之类的(想要一个更好的词)构造/概念. 在哪里"ANY"都适合任何数据类型的列(如NULL)，但是(与NULL不同，并且与所有其他特定值不同)将匹配 ANYTHING (包括NULL-?可能是敦促不知道).
我可以这样做:
SELECT NVL(whatever_column, ANY) from my_table;
我认为答案可能是否定的.并可能走开，NULL足够糟糕-不用介意您对此有半点想法的怪物".
不，SQL中没有等于所有值的通用接受器"值.
您可以做的是将NVL进行比较.就像您要进行JOIN一样:
SELECT ...
FROM my_table AS m
JOIN other_table AS o ON o.name = NVL(m.name, o.name)
因此，如果m.name为NULL，则联接会将o.name与o.name进行比较，这当然总是正确的.
对于NULL的其他用途，您可能必须使用另一种适合情况的技术.
Is there a concept (with an implementation - in Oracle SQL for starters) which behaves like a 'universal' matcher ?
What I mean is; I know NULL is not equal to anything - including NULL. Which is why you have to be careful to 'IS NULL' rather than '=NULL' in SQL expressions.
I also know it is useful to use the NVL (in Oracle) function to detect a NULL and replace it with something in the output.
However: what you replace the NULL with using NVL has to match the datatype of the underlying column; otherwise you'll (rightly) get an error.
An example:
I have a table with a NULLABLE column 'name' of type VARCHAR2; and this contains a NULL row.
I can fetch out the NULL and replace it with an NVL like this:
SELECT NVL(name, 'NullyMcNullFace’) from my_table;
Great.
But if the column happens to a NUMBER (say 'age'), then I have to change my NVL:
SELECT NVL(age, 32) from my_table;
Also great.
Now if the column happens to be a DATE (say 'somedate'), then I have to change my NVL again:
SELECT NVL(somedate, sysdate) from my_table;
What I'm getting at here : is that in order to deal with NULLs you have to replace with a specific something ; and that specific something has to 'fit' the data-type.
So is there a construct/concept of (for want of a better word) like 'ANY' here. Where 'ANY' would fit into a column of any datatype (like NULL), but (unlike NULL and unlike all other specific values) would match ANYTHING (including NULL - ? probably urghhh dunno).
So that I could do:
SELECT NVL(whatever_column, ANY) from my_table;
I think the answer is probably no; and probably 'go away, NULLs are bad enough - never mind this monster you have half-thought of'.
No, there's no "universal acceptor" value in SQL that is equal to everything.
What you can do is raise the NVL into your comparison. Like if you're trying to do a JOIN:
SELECT ...
FROM my_table AS m
JOIN other_table AS o ON o.name = NVL(m.name, o.name)
So if m.name is NULL, then the join will compare o.name to o.name, which is of course always true.
For other uses of NULL, you might have to use another technique that suits the situation.
这篇关于是否存在与SQL NULL相反的概念?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
