SQL 确定 Oracle null == null
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/191640/
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
Determine Oracle null == null
提问by James Curran
I wish to search a database table on a nullable column. Sometimes the value I'm search for is itself NULL. Since Null is equal to nothing, even NULL, saying
我希望在可空列上搜索数据库表。有时，我要搜索的值本身就是 NULL。由于 Null 等于没有，甚至 NULL，说
where MYCOLUMN=SEARCHVALUE
will fail. Right now I have to resort to
将失败。现在我不得不求助于
where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))
Is there a simpler way of saying that?
有没有更简单的说法？
(I'm using Oracle if that matters)
（如果这很重要，我正在使用 Oracle）
回答by Andy Lester
You can do the IsNull or NVL stuff, but it's just going to make the engine do more work. You'll be calling functions to do column conversions which then have to have the results compared.
你可以做 IsNull 或 NVL 的事情，但这只会让引擎做更多的工作。您将调用函数来进行列转换，然后必须比较结果。
Use what you have
使用你所拥有的
where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))
回答by DCookie
@Andy Lester asserts that the original form of the query is more efficient than using NVL. I decided to test that assertion:
@Andy Lester 断言查询的原始形式比使用 NVL 更有效。我决定测试这个断言：
SQL> DECLARE
2 CURSOR B IS
3 SELECT batch_id, equipment_id
4 FROM batch;
5 v_t1 NUMBER;
6 v_t2 NUMBER;
7 v_c1 NUMBER;
8 v_c2 NUMBER;
9 v_b INTEGER;
10 BEGIN
11 -- Form 1 of the where clause
12 v_t1 := dbms_utility.get_time;
13 v_c1 := dbms_utility.get_cpu_time;
14 FOR R IN B LOOP
15 SELECT COUNT(*)
16 INTO v_b
17 FROM batch
18 WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL);
19 END LOOP;
20 v_t2 := dbms_utility.get_time;
21 v_c2 := dbms_utility.get_cpu_time;
22 dbms_output.put_line('For clause: WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL)');
23 dbms_output.put_line('CPU seconds used: '||(v_c2 - v_c1)/100);
24 dbms_output.put_line('Elapsed time: '||(v_t2 - v_t1)/100);
25
26 -- Form 2 of the where clause
27 v_t1 := dbms_utility.get_time;
28 v_c1 := dbms_utility.get_cpu_time;
29 FOR R IN B LOOP
30 SELECT COUNT(*)
31 INTO v_b
32 FROM batch
33 WHERE NVL(equipment_id,'xxxx') = NVL(R.equipment_id,'xxxx');
34 END LOOP;
35 v_t2 := dbms_utility.get_time;
36 v_c2 := dbms_utility.get_cpu_time;
37 dbms_output.put_line('For clause: WHERE NVL(equipment_id,''xxxx'') = NVL(R.equipment_id,''xxxx'')');
38 dbms_output.put_line('CPU seconds used: '||(v_c2 - v_c1)/100);
39 dbms_output.put_line('Elapsed time: '||(v_t2 - v_t1)/100);
40 END;
41 /
For clause: WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL)
CPU seconds used: 84.69
Elapsed time: 84.8
For clause: WHERE NVL(equipment_id,'xxxx') = NVL(R.equipment_id,'xxxx')
CPU seconds used: 124
Elapsed time: 124.01
PL/SQL procedure successfully completed
SQL> select count(*) from batch;
COUNT(*)
----------
20903
SQL>
I was kind of surprised to find out just how correct Andy is. It costs nearly 50% more to do the NVL solution. So, even though one piece of code might not look as tidy or elegant as another, it could be significantly more efficient. I ran this procedure multiple times, and the results were nearly the same each time. Kudos to Andy...
我有点惊讶地发现安迪是多么正确。执行 NVL 解决方案的成本要高出近 50%。因此，即使一段代码看起来不像另一段代码那样整洁或优雅，但它可以显着提高效率。我多次运行此程序，每次的结果几乎相同。向安迪致敬...
回答by Chris Shaffer
I don't know if it's simpler, but I've occasionally used
不知道是不是更简单，但我偶尔用过
WHERE ISNULL(MyColumn, -1) = ISNULL(SearchValue, -1)
Replacing "-1" with some value that is valid for the column type but also not likely to be actually found in the data.
用一些对列类型有效但也不太可能在数据中实际找到的值替换“-1”。
NOTE: I use MS SQL, not Oracle, so not sure if "ISNULL" is valid.
注意：我使用 MS SQL，而不是 Oracle，所以不确定“ISNULL”是否有效。
回答by Peter Meinl
In Expert Oracle Database ArchitectureI saw:
在专家 Oracle 数据库架构中，我看到了：
WHERE DECODE(MYCOLUMN, SEARCHVALUE, 1) = 1
回答by JosephStyons
Use NVL to replace null with some dummy value on both sides, as in:
使用 NVL 将两边的一些虚拟值替换为 null，如下所示：
WHERE NVL(MYCOLUMN,0) = NVL(SEARCHVALUE,0)
回答by Vinko Vrsalovic
Another alternative, which is probably optimal from the executed query point of view, and will be useful only if you are doing some kind of query generationis to generate the exact query you need based on the search value.
另一种选择，从执行的查询的角度来看可能是最佳的，并且只有在您进行某种查询生成时才有用，即根据搜索值生成您需要的确切查询。
Pseudocode follows.
伪代码如下。
if (SEARCHVALUE IS NULL) {
condition = 'MYCOLUMN IS NULL'
} else {
condition = 'MYCOLUMN=SEARCHVALUE'
}
runQuery(query,condition)
回答by Ted
If an out-of-band value is possible:
如果带外值是可能的：
where coalesce(mycolumn, 'out-of-band')
= coalesce(searchvalue, 'out-of-band')
回答by EvilTeach
This can also do the job in Oracle.
这也可以在 Oracle 中完成这项工作。
WHERE MYCOLUMN || 'X' = SEARCHVALUE || 'X'
There are some situations where it beats the IS NULL test with the OR.
在某些情况下，它用 OR 击败了 IS NULL 测试。
I was also surprised that DECODE lets you check NULL against NULL.
我也很惊讶 DECODE 可以让您根据 NULL 检查 NULL。
WITH
TEST AS
(
SELECT NULL A FROM DUAL
)
SELECT DECODE (A, NULL, 'NULL IS EQUAL', 'NULL IS NOT EQUAL')
FROM TEST
回答by DCookie
Try
尝试
WHERE NVL(mycolumn,'NULL') = NVL(searchvalue,'NULL')
回答by Carl
I would think that what you have is OK. You could maybe use:
我认为你所拥有的一切都很好。你也许可以使用：
where NVL(MYCOLUMN, '') = NVL(SEARCHVALUE, '')
