Я хочу искать таблицу базы данных в столбце с нулевым значением. Иногда значение, которое я ищу, само по себе является NULL. Поскольку Null равно ничто, даже NULL, говоря
where MYCOLUMN=SEARCHVALUE
не удастся. Прямо сейчас я должен прибегнуть к
where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))
Есть ли более простой способ сказать это?
(Я использую Oracle, если это имеет значение)
Вы можете делать вещи IsNull или NVL, но это просто заставит двигатель сделать больше работы. Вы будете вызывать функции для преобразования столбцов, которые затем должны сравнивать результаты.
Используйте то, что у вас есть
where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))
@Энди Лестер утверждает, что исходная форма запроса более эффективна, чем использование 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>
Я был удивлен, узнав, насколько правдивый Энди. Это почти на 50% больше, чем решение NVL. Таким образом, хотя один фрагмент кода может выглядеть не таким аккуратным или элегантным, как другой, он может быть значительно более эффективным. Я выполнял эту процедуру несколько раз, и результаты были почти одинаковы каждый раз. Престижность Энди...
В Экспертная архитектура Oracle Database Я видел:
WHERE DECODE(MYCOLUMN, SEARCHVALUE, 1) = 1
Я не знаю, проще ли это, но я иногда использовал
WHERE ISNULL(MyColumn, -1) = ISNULL(SearchValue, -1)
Замена "-1" на некоторое значение, которое допустимо для типа столбца, но также вряд ли будет действительно найдено в данных.
ПРИМЕЧАНИЕ. Я использую MS SQL, а не Oracle, поэтому не уверен, что допустимо "ISNULL".
Используйте 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')
Это также может выполнять работу в Oracle.
WHERE MYCOLUMN || 'X' = SEARCHVALUE || 'X'
Есть некоторые ситуации, когда он превосходит тест IS NULL с помощью OR.
Я также был удивлен, что DECODE позволяет вам проверять NULL на NULL.
WITH
TEST AS
(
SELECT NULL A FROM DUAL
)
SELECT DECODE (A, NULL, 'NULL IS EQUAL', 'NULL IS NOT EQUAL')
FROM TEST
Это ситуация, в которой мы находимся во многом с нашими функциями 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)
Я думаю, что у вас все в порядке. Вы могли бы использовать:
where NVL(MYCOLUMN, '') = NVL(SEARCHVALUE, '')