NO EN la cláusula y valores NULOS

Cada vez que usas NULL, realmente estás tratando con una lógica de Tres valores.

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

    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

Lo DESCONOCIDO no es lo mismo que FALSO puedes probarlo fácilmente llamando a:

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

Ambas consultas no le darán resultados

Si lo DESCONOCIDO era lo mismo que FALSE entonces suponiendo que la primera consulta le daría FALSE la segunda tendría que evaluar a TRUE como lo haría han sido lo mismo que NO (FALSO).
Ese no es el caso.

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

Todo el tema de los NULOs y la Lógica de Tres Valores puede ser un poco confuso al principio, pero es esencial entenderlo para escribir consultas correctas en TSQL

Otro artículo que recomendaría es SQL Aggregate Functions y NULL.

Comparar con null es indefinido, a menos que utilice ES NULL.

Entonces, al comparar 3 con NULL (consulta A), devuelve undefined.

Es decir, SELECCIONE 'true' donde 3 in (1,2, null) y Seleccione 'true' donde 3 not in (1,2, null)

Producirá el mismo resultado, ya que NO (INDEFINIDO) sigue siendo indefinido, pero no VERDADERO

NOT IN devuelve 0 registros cuando se compara con un valor desconocido

Dado que NULL es un desconocido, una consulta NOT IN que contiene 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 es el valor que se está probando.

El título de esta pregunta en el momento de escribir este artículo es

SQL NO EN los valores de restricción y NULL

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

Sin embargo, especialmente dada la redacción del título, quiero señalar que algunas declaraciones hechas aquí son declaraciones potencialmente engañosas, aquellas en la línea de (parafrasear)

Cuando el predicado evalúa a DESCONOCIDO no obtiene ninguna fila.

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 abordadas en una excelente respuesta de @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 (usando IN) se evalúa como VERDADERA y la segunda restricción (usando 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 obtienes ninguna fila" porque de hecho tenemos una fila insertada como resultado.

El efecto anterior es de hecho el correcto con respecto 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 la es una tabla de esas filas de T para cuál es el resultado de la condición de búsqueda verdadero.

4.10 restricciones de Integridad

Una restricción de comprobación de tabla se cumple si y solo si el la condición de búsqueda no es falsa para ninguna fila de una tabla.

En otras palabras:

En SQL DML, las filas se eliminan del resultado cuando el WHERE se evalúa como DESCONOCIDO porque no satisface 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 cumple la condición "no es falsa".

Aunque los efectos en SQL DML y SQL DDL respectivamente pueden parecer contradictorios, hay 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 no fallar en satisfacer una restricción): sin este comportamiento, cada restricción tendría que manejar explícitamente nulls y eso sería muy insatisfactorio desde un diseño de lenguaje perspectiva (por no mencionar, un dolor adecuado para los codificadores!)

P. S. si usted está encontrando tan difícil seguir la lógica como "desconocido no deja de satisfacer una restricción" como estoy para escribirlo, entonces considere que puede prescindir de todo esto simplemente evitando columnas nullables en SQL DDL y cualquier cosa en SQL DML que produce nulls (por ejemplo, uniones externas)!

En A, 3 se prueba para la igualdad contra cada miembro del conjunto, cediendo (FALSO, FALSO, VERDADERO, DESCONOCIDO). Dado que uno de los elementos es VERDADERO, la condición es VERDADERA. (También es posible que algún cortocircuito tenga lugar aquí, por lo que en realidad se detiene tan pronto como golpea el primer VERDADERO y nunca evalúa 3=NULL.)

En B, creo que está evaluando la condición como NO (3 en (1,2,nulo)). Probando 3 para la igualdad contra los rendimientos del conjunto (FALSE, FALSE, UNKNOWN), que se agrega a DESCONOCIDO. NO (DESCONOCIDO ) rendimientos DESCONOCIDOS. Así que, en general, la verdad de la condición es desconocida, que al final se trata esencialmente como FALSA.

Null significa y ausencia de datos, es decir, es desconocido, no un valor de datos de nada. Es muy fácil para las personas de un fondo de programación confundir esto porque en lenguajes de tipo C cuando se utilizan punteros null es de hecho nada.

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

En el segundo, sin embargo, puede reducirlo a

seleccione 'true' donde 3 not in (null)

Así que nada se devuelve porque el parser no sabe nada sobre el conjunto con el que lo está comparando - no es un conjunto vacío, sino un conjunto desconocido. Usar (1, 2, null) no ayuda porque el conjunto (1,2) es obviamente falso, pero entonces estás and'ing eso contra desconocido, que es desconocido.

SI desea filtrar con NOT IN para una subconsulta con NULLs justcheck para not null

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

Se puede concluir de las respuestas aquí que NOT IN (subquery) no maneja los nulos correctamente y debe evitarse a favor de NOT EXISTS. Sin embargo, tal conclusión puede ser prematura. En el siguiente escenario, acreditado a Chris Date (Database Programming and Design, Vol 2 No 9, Septiembre de 1989), 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 contiene actualmente 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 los proveedores que son conocidos número de parte de suministro 'P1', pero no en cantidades de 1000.

Los siguientes usos NOT IN para identificar correctamente solo al proveedor "S2":

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 siguiente consulta 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 por suministrar piezas
• spq proveedores conocidos piezas de suministro en cantidades conocidas

Teniendo en cuenta que probablemente debería haber una restricción de clave foránea donde spq referencias sp.

El resultado se puede obtener usando el operador relacional 'menos' (siendo 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;

Esto es para Boy:

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 ansi

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