uso - tipos de case en sql

Obtener nulo== nulo en SQL

Question

Deseo buscar una tabla de base de datos en una columna que se pueda anotar. Algunas veces, el valor que busco es NULL. Dado que Null es igual a nada, incluso NULL, diciendo

where MYCOLUMN=SEARCHVALUE 

fallará. En este momento tengo que recurrir a

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

¿Hay alguna manera más simple de decir eso?

(Estoy usando Oracle si eso importa)

Answer 1

@Andy Lester afirma que la forma original de la consulta es más eficiente que usar NVL. Decidí probar esa afirmación:

    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> 

Me sorprendió bastante descubrir cuán correcto es Andy. Cuesta casi un 50% más hacer la solución NVL. Por lo tanto, aunque una pieza de código no se vea tan ordenada o elegante como otra, podría ser significativamente más eficiente. Ejecuté este procedimiento varias veces y los resultados fueron casi los mismos cada vez. Felicitaciones a Andy ...

Answer 2

En Expert Oracle Database Architecture vi:

WHERE DECODE(MYCOLUMN, SEARCHVALUE, 1) = 1

Answer 3

Esta es una situación en la que nos encontramos mucho con nuestras funciones de Oracle que generan informes. Queremos permitir a los usuarios ingresar un valor para restringir los resultados o dejarlo en blanco para devolver todos los registros. Esto es lo que he usado y nos ha funcionado bien.

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)

Answer 4

Esto también puede hacer el trabajo en Oracle.

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

Hay algunas situaciones en las que supera la prueba IS NULL con el quirófano.

También me sorprendió que DECODE te permita verificar NULL contra NULL.

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

Answer 5

No sé si es más simple, pero ocasionalmente he usado

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

Reemplazar "-1" con algún valor que sea válido para el tipo de columna, pero que probablemente no se encuentre realmente en los datos.

NOTA: uso MS SQL, no Oracle, por lo que no estoy seguro de si "ISNULL" es válido.

Answer 6

Otra alternativa, que probablemente sea óptima desde el punto de vista de la consulta ejecutada, y será útil solo si está realizando algún tipo de generación de consultas es generar la consulta exacta que necesita basándose en el valor de búsqueda.

El pseudocódigo sigue.

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

Answer 7

Puedes hacer las cosas de IsNull o NVL, pero solo hará que el motor trabaje más. Llamarás a funciones para hacer conversiones de columna que luego tendrán que comparar los resultados.

Usa lo que tienes

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

Answer 8

Si un valor fuera de banda es posible:

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

Answer 9

Tratar

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

Answer 10

Use NVL para reemplazar null con algún valor ficticio en ambos lados, como en:

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

Answer 11

Yo pensaría que lo que tienes está bien. Quizás puedas usar:

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