在SQL中获取null==null

你可以做IsNull或者NVL之类的事情,但这只会让引擎做更多的工作。您将调用函数进行列转换,然后必须对结果进行比较。

利用你所拥有的

where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))

@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> 

我很惊讶地发现安迪是多么正确。使用NVL解决方案的成本增加了近50%。因此,即使一段代码看起来可能不像另一段代码那么整洁或优雅,但它可能会大大提高效率。我多次运行此过程,每次结果几乎相同。安迪的荣誉。。。

在里面 Expert Oracle Database Architecture 我看到:

WHERE DECODE(MYCOLUMN, SEARCHVALUE, 1) = 1

我不知道它是否更简单,但我偶尔会使用

WHERE ISNULL(MyColumn, -1) = ISNULL(SearchValue, -1)

将“-1”替换为对列类型有效但在数据中实际不太可能找到的值。

使用NVL在两侧用一些伪值替换null,如:

WHERE NVL(MYCOLUMN,0) = NVL(SEARCHVALUE,0)

另一种选择,从已执行查询的角度来看可能是最佳的,以及 只有在执行某种查询生成时才有用 是根据搜索值生成所需的精确查询。

if (SEARCHVALUE IS NULL) {
    condition = 'MYCOLUMN IS NULL'
} else {
    condition = 'MYCOLUMN=SEARCHVALUE'
}
runQuery(query,condition)

如果可能存在带外值:

where coalesce(mycolumn, 'out-of-band') 
    = coalesce(searchvalue, 'out-of-band')

WHERE NVL(mycolumn,'NULL') = NVL(searchvalue,'NULL')

WHERE MYCOLUMN || 'X'  = SEARCHVALUE || 'X'

在某些情况下,它会使用OR来击败IS NULL测试。

WITH 
TEST AS
(
    SELECT NULL A FROM DUAL
)
SELECT DECODE (A, NULL, 'NULL IS EQUAL', 'NULL IS NOT EQUAL')
FROM TEST

我认为你所拥有的一切都是可以的。您可以使用:

where NVL(MYCOLUMN, '') = NVL(SEARCHVALUE, '')

在这种情况下,我们发现我们的Oracle功能可以驱动报表。我们希望允许用户输入一个值来限制结果,或者将其留空以返回所有记录。这就是我所使用的,它对我们很有效。

WHERE rte_pending.ltr_rte_id = prte_id
  OR ((rte_pending.ltr_rte_id IS NULL OR rte_pending.ltr_rte_id IS NOT NULL)
      AND prte_id IS NULL)