sql server - switch - ¿Cuándo "NO" no es una negación?

sql case example

Question

¿Por qué ambos de los siguientes devuelven cero? ¿Seguramente el segundo es una negación del primero? Estoy usando SQL Server 2008.

DECLARE 
    @a VARCHAR(10) = NULL ,
    @b VARCHAR(10) = ''a''

SELECT  
    CASE WHEN ( ( @a IS NULL
                      AND @b IS NULL
                    )
                    OR @a = @b
                  ) THEN 1
         ELSE 0
    END , -- Returns 0
    CASE WHEN NOT ( ( @a IS NULL
                      AND @b IS NULL
                    )
                    OR @a = @b
                  ) THEN 1
         ELSE 0
    END -- Also returns 0

Answer 1

Es un problema con @ a = @ b si cualquiera de este valor es nulo entonces será un problema

Si intenta a continuación el código dará resultados correctos

DECLARE 
    @a VARCHAR(10) = NULL ,
    @b VARCHAR(10) = ''a''

SELECT  
    CASE WHEN ( ( @a IS NULL
                      AND @b IS NULL
                    )
                    OR @a = @b
                  ) THEN 1
         ELSE 0
    END , -- returns 0
    CASE WHEN NOT ( ( @a IS NULL
                      AND @b IS NULL
                    )
                    OR ISNULL(@a,-1) = ISNULL(@b,-1)
                  ) THEN 1
         ELSE 0
    END -- also returns 0

Answer 2

Es una negación. Sin embargo, debe comprender ANSI NULLs: una negación de NULL también es NULL. Y NULL es un falso valor de verdad.

Por lo tanto, si alguno de tus argumentos es nulo, el resultado de @a = @b será nulo (falsy), y una negación de eso también será nulo (falsy).

Para usar la negación de la manera que desee, debe deshacerse de NULL. Sin embargo, podría ser más sencillo simplemente invertir los resultados de la comparación:

case when (...) then 1 else 0 end,
case when (...) then 0 else 1 end

Lo cual siempre le dará 1, 0 o 0, 1 .

EDITAR:

Como señaló jpmc26, podría ser útil ampliar un poco sobre cómo se comportan los nulos para que no se tenga la idea de que un único NULL hará que todo sea NULL . Hay operadores que no siempre devuelven null cuando uno de sus argumentos es nulo; el ejemplo más obvio is null , por supuesto.

En un ejemplo más amplio, los operadores lógicos en T-SQL usan el álgebra de Kleene (o algo similar), que define los valores de verdad de una expresión OR como esta:

  | T | U | F
T | T | T | T
U | T | U | U
F | T | U | F

( AND es análogo, como lo son los otros operadores)

Entonces puede ver que si al menos uno de los argumentos es verdadero, el resultado también será verdadero, incluso si el otro es desconocido ("nulo"). Lo cual también significa que not(T or U) le dará un valor de verdad falso, mientras que not(F or U) también le dará un valor de verdad falso, a pesar de que F or U son falsos, ya que F or U es U , y not(U) también es U , que es falso.

Esto es importante para explicar por qué su expresión funciona de la manera que espera cuando ambos argumentos son nulos: el @a is null and @b is null evalúa como verdadero, y true or unknown evalúa como true .

Answer 3

Este comportamiento "extraño" que está encontrando es causado por los valores NULL .

La negación de NOT (Something that returns NULL) no es TRUE , sigue siendo NULL .

P.EJ

SELECT * FROM <Table> WHERE <Column> = null -- 0 rows 
SELECT * FROM <Table> WHERE NOT (<Column> = null) -- Still 0 rows

Además de lo que se ha dicho aquí, puedes evitar ese comportamiento usando

SET ANSI_NULLS OFF

Lo cual permitirá que el optimizador trate NULL como valor normal y devuelva TRUE/FALSE . ¡Debe tener en cuenta que esto no es recomendable en absoluto y debe evitarlo!

Answer 4

NOT es siempre una negación. El motivo de este comportamiento de T-SQL reside en el hecho de que null valores null se tratan de una manera especial en función de la configuración de la base de datos (conocida como ansi_nulls ). Según esta configuración, null se trata de la misma manera que cualquier otro valor o se trata como "valor no establecido". En este caso, todas las expresiones que contienen valores nulos se consideran no válidas.

Además, la expresión

(@a IS NULL AND @b IS NULL)
OR 
@a = @b

cubre solo el caso cuando ambas variables son NULL , no trata con casos cuando @a o @b es NULL . Si eso sucede, el resultado depende de la configuración de ansi_nulls : si está ansi_nulls , el resultado de @a = @b siempre es false si una de las variables es NULL .

Si ansi_nulls está off , entonces NULL se trata como un valor y se comporta como esperaba.

Para evitar un comportamiento inesperado, debe cubrir todos los casos de la siguiente manera:

DECLARE 
    @a VARCHAR(10) = ''a'',
    @b VARCHAR(10) = null

SELECT  
    CASE 
        WHEN (@a IS NOT null AND @b IS null) THEN 0
        WHEN (@a IS null AND @b IS NOT null) THEN 0
        WHEN (@a IS null AND @b IS null) THEN 1
        WHEN (@a=@b) THEN 1
    ELSE 0
    END 

Tenga en cuenta que en este ejemplo se tratan todos los casos nulos antes de que se marque el caso @a=@b (en una instrucción CASE , los WHEN se procesan en el orden en que aparecen, y si una condición coincide, el procesamiento finaliza y se devuelve el valor especificado).

Para probar todas las combinaciones posibles (relevantes), puede usar este script:

DECLARE @combinations TABLE (
    a VARCHAR(10),b VARCHAR(10)
    )

INSERT INTO @combinations
    SELECT ''a'', null 
    UNION SELECT null, ''b''
    UNION SELECT ''a'', ''b''
    UNION SELECT null, null
    UNION SELECT ''a'', ''a''

SELECT a, b,
    CASE 
        WHEN (a IS NOT null AND b IS null) THEN 0
        WHEN (a IS null AND b IS NOT null) THEN 0
        WHEN (a IS null AND b IS null) THEN 1
        WHEN (a=b) THEN 1
    ELSE 0
    END as result
from @combinations
order by result 

Vuelve:

En otras palabras, en este script, null se trata como un valor, por lo tanto a=''a'' y b=null devuelve 0 , que es lo que esperaba. Solo si ambas variables son iguales (o ambas null ), devuelve 1 .