sql-server - validar - is null sql server ejemplos

NOT IN subquery falla cuando hay resultados con valores NULL

Question

Lo siento muchachos, no tenía ni idea de cómo expresar esto, pero tengo lo siguiente en una cláusula where:

person_id not in (
    SELECT distinct person_id
    FROM protocol_application_log_devl pal
    WHERE pal.set_id = @set_id
)

Cuando la subconsulta no devuelve resultados, mi selección completa no puede devolver nada. Para person_id esto, reemplacé person_id en la subconsulta con isnull(person_id, ''00000000-0000-0000-0000-000000000000'') .

Parece que funciona, pero ¿hay una mejor manera de resolver esto?

Answer 1

Es mejor utilizar NOT EXISTS todos modos:

WHERE NOT EXISTS(
    SELECT 1 FROM protocol_application_log_devl pal
    WHERE pal.person_id = person_id
     AND  pal.set_id = @set_id
)

¿Debo usar NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, o NOT EXISTS?

Un patrón que veo bastante, y ojalá que no, NO ESTÉ EN. Cuando veo este patrón, me estremezco. Pero no por razones de rendimiento, después de todo, crea un plan lo suficientemente decente en este caso:

El principal problema es que los resultados pueden ser sorprendentes si la columna de destino es NULLable (SQL Server procesa esto como una anti semi unión izquierda, pero no puede decir de manera confiable si un NULL en el lado derecho es igual ao no igual a - la referencia en el lado izquierdo). Además, la optimización puede comportarse de manera diferente si la columna es NULLable, incluso si en realidad no contiene ningún valor NULL

En lugar de NO EN, use un NOT correlacionado para este patrón de consulta. Siempre . Otros métodos pueden competir en términos de rendimiento, cuando todas las demás variables son iguales, pero todos los otros métodos presentan problemas de rendimiento u otros desafíos.

Answer 2

Si bien apoyo la respuesta de Tim como correcta en la práctica (NOT IN no es apropiado aquí), este es un caso interesante anotado en la documentación IN / NOT IN :

Precaución: Todos los valores nulos devueltos por subconsulta o expresión que se comparan con test_expression utilizando IN o NOT IN retorno UNKNOWN. El uso de valores nulos junto con IN o NOT IN puede producir resultados inesperados ¹ .

Esta es la razón por la cual isnull "soluciona" el problema: enmascara cualquiera de dichos valores NULL y evita el comportamiento inesperado. Con esto en mente, el siguiente enfoque también funcionaría (pero por favor tenga en cuenta el consejo de no usar NOT IN para comenzar):

person_id not in (
  SELECT distinct person_id
  FROM protocol_application_log_devl pal
  WHERE pal.set_id = @set_id
    AND person_id NOT NULL    -- guard here
)

Sin embargo, un NULL person_id es sospechoso y podría indicar otros problemas.

¹ Aquí está el pudín de prueba:

select case when 1 not in (2)       then 1 else 0 end as r1,
       case when 1 not in (2, NULL) then 1 else 0 end as r2
-- r1: 1, r2: 0