sql - vacio - Cláusula NOT IN y valores NULL

reemplazar null por vacio sql

Question

Este problema surgió cuando obtuve diferentes conteos de registros para lo que pensé que eran consultas idénticas, una que not in restricción y la otra una left join . La tabla en la que not in restricción tenía un valor nulo (datos incorrectos) que hizo que la consulta devolviera un conteo de 0 registros. Entiendo por qué, pero podría necesitar alguna ayuda para comprender completamente el concepto.

Para decirlo simplemente, ¿por qué la consulta A devuelve un resultado pero B no?

A: select ''true'' where 3 in (1, 2, 3, null)
B: select ''true'' where 3 not in (1, 2, null)

Esto estaba en SQL Server 2005. También encontré que llamar a set ansi_nulls off hace que B devuelva un resultado.

Answer 1

NOT IN devuelve 0 registros en comparación con un valor desconocido

Como NULL es una incógnita, una consulta NOT IN contenga un NULL o NULL s en la lista de valores posibles siempre devolverá 0 registros, ya que no hay forma de estar seguro de que el valor NULL no sea el valor que se está probando.

Answer 2

Compare to null no está definido, a menos que use IS NULL.

Por lo tanto, al comparar 3 con NULL (consulta A), devuelve undefined.

Es decir, SELECCIONE ''verdadero'' donde 3 en (1,2, nulo) y SELECCIONE ''verdadero'' donde 3 no está en (1,2, nulo)

producirá el mismo resultado, ya que NOT (UNDEFINED) aún no está definido, pero no es TRUE

Answer 3

Cuando usas NULL, realmente estás tratando con una lógica de tres valores.

Su primera consulta devuelve resultados cuando la cláusula WHERE se evalúa como:

    3 = 1 or 3 = 2 or 3 = 3 or 3 = null
which is:
    FALSE or FALSE or TRUE or UNKNOWN
which evaluates to 
    TRUE

El segundo:

    3 <> 1 and 3 <> 2 and 3 <> null
which evaluates to:
    TRUE and TRUE and UNKNOWN
which evaluates to:
    UNKNOWN

DESCONOCIDO no es lo mismo que FALSO, puede probarlo fácilmente llamando a:

select ''true'' where 3 <> null
select ''true'' where not (3 <> null)

Ambas consultas no te darán resultados.

Si el valor DESCONOCIDO era el mismo que FALSO, suponiendo que la primera consulta le daría FALSO, el segundo tendría que evaluar a VERDADERO como habría sido el mismo que NO (FALSO).
Ese no es el caso.

Hay un muy buen artículo sobre este tema en SqlServerCentral .

Todo el problema de NULL y la lógica de tres valores puede ser un poco confuso al principio, pero es esencial comprenderlo para escribir las consultas correctas en TSQL.

Otro artículo que recomendaría es Funciones agregadas de SQL y NULL .

Answer 4

De las respuestas aquí se puede concluir que NOT IN (subquery) no maneja los nulos correctamente y debe evitarse en favor de NOT EXISTS . Sin embargo, tal conclusión puede ser prematura. En el siguiente escenario, acreditado a Chris Date (Diseño y programación de bases de datos, Vol. 2, No 9, septiembre de 1989), NOT IN es NOT IN que maneja los nulos correctamente y devuelve el resultado correcto, en lugar de NOT EXISTS .

Considere una tabla sp para representar a los proveedores ( sno ) que se sabe que suministran piezas ( pno ) en cantidad ( qty ). La tabla actualmente tiene los siguientes valores:

      VALUES (''S1'', ''P1'', NULL), 
             (''S2'', ''P1'', 200),
             (''S3'', ''P1'', 1000)

Tenga en cuenta que la cantidad es anulable, es decir, para poder registrar el hecho de que se sabe que un proveedor suministra piezas, incluso si no se sabe en qué cantidad.

La tarea es encontrar a los proveedores que son conocidos y suministrar el número de pieza ''P1'' pero no en cantidades de 1000.

Los siguientes usos NOT IN para identificar correctamente al proveedor ''S2'' solamente:

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( ''S1'', ''P1'', NULL ), 
                       ( ''S2'', ''P1'', 200 ),
                       ( ''S3'', ''P1'', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT DISTINCT spx.sno
  FROM sp spx
 WHERE spx.pno = ''P1''
       AND 1000 NOT IN (
                        SELECT spy.qty
                          FROM sp spy
                         WHERE spy.sno = spx.sno
                               AND spy.pno = ''P1''
                       );

Sin embargo, la consulta a continuación utiliza la misma estructura general pero con NOT EXISTS pero incluye incorrectamente el proveedor ''S1'' en el resultado (es decir, para el que la cantidad es nula):

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( ''S1'', ''P1'', NULL ), 
                       ( ''S2'', ''P1'', 200 ),
                       ( ''S3'', ''P1'', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT DISTINCT spx.sno
  FROM sp spx
 WHERE spx.pno = ''P1''
       AND NOT EXISTS (
                       SELECT *
                         FROM sp spy
                        WHERE spy.sno = spx.sno
                              AND spy.pno = ''P1''
                              AND spy.qty = 1000
                      );

¡Así que NOT EXISTS no es la bala de plata que pudo haber aparecido!

Por supuesto, la fuente del problema es la presencia de nulos, por lo tanto, la solución "real" es eliminar esos nulos.

Esto se puede lograr (entre otros diseños posibles) utilizando dos tablas:

• sp proveedores conocidos para suministrar piezas
• spq proveedores conocidos para suministrar piezas en cantidades conocidas

teniendo en cuenta que probablemente debería haber una restricción de clave externa donde spq references sp .

El resultado se puede obtener utilizando el operador relacional ''menos'' (que es la palabra clave EXCEPT en SQL estándar), por ejemplo

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( ''S1'', ''P1'' ), 
                       ( ''S2'', ''P1'' ),
                       ( ''S3'', ''P1'' ) )
              AS T ( sno, pno )
     ),
     spq AS 
     ( SELECT * 
         FROM ( VALUES ( ''S2'', ''P1'', 200 ),
                       ( ''S3'', ''P1'', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT sno
  FROM spq
 WHERE pno = ''P1''
EXCEPT 
SELECT sno
  FROM spq
 WHERE pno = ''P1''
       AND qty = 1000;

Answer 5

El título de esta pregunta en el momento de la escritura es

Restricción de SQL NOT IN y valores NULL

Del texto de la pregunta, parece que el problema estaba ocurriendo en una consulta SELECT DML de SQL, en lugar de una CONSTRAINT DDL de SQL.

Sin embargo, especialmente teniendo en cuenta la redacción del título, quiero señalar que algunas de las declaraciones que se hacen aquí son declaraciones potencialmente engañosas, como la de (parafraseando)

Cuando el predicado se evalúa como DESCONOCIDO, no se obtienen filas.

Aunque este es el caso de SQL DML, al considerar las restricciones, el efecto es diferente.

Considere esta tabla muy simple con dos restricciones tomadas directamente de los predicados en la pregunta (y abordada en una excelente respuesta por @Brannon):

DECLARE @T TABLE 
(
 true CHAR(4) DEFAULT ''true'' NOT NULL, 
 CHECK ( 3 IN (1, 2, 3, NULL )), 
 CHECK ( 3 NOT IN (1, 2, NULL ))
);

INSERT INTO @T VALUES (''true'');

SELECT COUNT(*) AS tally FROM @T;

Según la respuesta de @ Brannon, la primera restricción (que usa IN ) se evalúa como VERDADERA y la segunda restricción (que usa NOT IN ) se evalúa como DESCONOCIDA. Sin embargo , la inserción tiene éxito! Por lo tanto, en este caso no es estrictamente correcto decir: "no obtiene ninguna fila" porque de hecho se insertó una fila como resultado.

El efecto anterior es, de hecho, el correcto en lo que respecta al estándar SQL-92. Compare y contraste la siguiente sección de la especificación SQL-92

7.6 donde la cláusula

El resultado de es una tabla de esas filas de T para las cuales el resultado de la condición de búsqueda es verdadero.

4.10 Restricciones de integridad

Se cumple una restricción de comprobación de tabla solo si la condición de búsqueda especificada no es falsa para ninguna fila de una tabla.

En otras palabras:

En SQL DML, las filas se eliminan del resultado cuando el WHERE evalúa como DESCONOCIDO porque no cumple la condición "es verdadero".

En SQL DDL (es decir, restricciones), las filas no se eliminan del resultado cuando se evalúan como DESCONOCIDAS porque satisface la condición "no es falso".

Aunque los efectos en SQL DML y SQL DDL respectivamente pueden parecer contradictorios, existe una razón práctica para dar a los resultados DESCONOCIDOS el "beneficio de la duda" al permitirles satisfacer una restricción (más correctamente, permitiéndoles que no dejen de satisfacer una restricción) : sin este comportamiento, todas las restricciones tendrían que manejar explícitamente los valores nulos y eso sería muy insatisfactorio desde la perspectiva del diseño del lenguaje (sin mencionar, ¡un dolor para los programadores!)

ps. si le resulta difícil seguir una lógica tal como "desconocido no deja de satisfacer una restricción" como lo hago para escribirla, entonces considere que puede prescindir de todo esto simplemente evitando las columnas que admiten valores nulos en SQL DDL y cualquier cosa en SQL DML que produce nulos (por ejemplo, combinaciones externas)!

Answer 6

En A, 3 se prueba la igualdad con respecto a cada miembro del conjunto, dando como resultado (FALSO, FALSO, VERDADERO, DESCONOCIDO). Dado que uno de los elementos es VERDADERO, la condición es VERDADERA. (También es posible que ocurra algún cortocircuito aquí, por lo que realmente se detiene tan pronto como llega a la primera VERDADERA y nunca evalúa 3 = NULL).

En B, creo que está evaluando la condición como NO (3 en (1,2, nulo)). Prueba 3 para la igualdad frente a los rendimientos establecidos (FALSO, FALSO, DESCONOCIDO), que se agrega a DESCONOCIDO. NO (DESCONOCIDO) rinde DESCONOCIDO. Entonces, en general, se desconoce la verdad de la condición, que al final se trata esencialmente como FALSO.

Answer 7

La consulta A es la misma que:

select ''true'' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Como 3 = 3 es verdadero, obtienes un resultado.

La consulta B es la misma que:

select ''true'' where 3 <> 1 and 3 <> 2 and 3 <> null

Cuando ansi_nulls está ansi_nulls , 3 <> null es DESCONOCIDO, por lo que el predicado se evalúa como DESCONOCIDO, y no obtiene ninguna fila.

Cuando ansi_nulls está desactivado, 3 <> null es verdadero, por lo que el predicado se evalúa como verdadero y se obtiene una fila.

Answer 8

Nulo significa y ausencia de datos, es decir, se desconoce, no es un valor de datos de nada. Es muy fácil para la gente de un fondo de programación confundir esto porque en los lenguajes de tipo C cuando se usan punteros nulos no es nada.

Por lo tanto, en el primer caso, 3 se encuentra en el conjunto de (1,2,3, nulo), por lo que se devuelve true

En el segundo sin embargo puedes reducirlo a

seleccione ''verdadero'' donde 3 no está en (nulo)

Entonces, no se devuelve nada porque el analizador no sabe nada sobre el conjunto con el que lo está comparando, no es un conjunto vacío sino un conjunto desconocido. El uso de (1, 2, null) no ayuda porque el conjunto (1,2) es obviamente falso, pero luego estás y lo contra lo desconocido, lo cual es desconocido.

Answer 9

Si desea filtrar con NOT IN para una subconsulta que contenga NULLs, simplemente marque para no null

SELECT blah FROM t WHERE blah NOT IN
        (SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT NULL )

Answer 10

esto es para chico:

select party_code 
from abc as a
where party_code not in (select party_code 
                         from xyz 
                         where party_code = a.party_code);

esto funciona independientemente de la configuración de ansi

Answer 11

también esto podría ser útil para conocer la diferencia lógica entre la unión, existe y en http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx