Odwróć wyrażenie logiczne, które może zwrócić NIEZNANY

Przykład

Mam stolik

ID  myField
------------
 1  someValue
 2  NULL
 3  someOtherValue

oraz wyrażenie logiczne T-SQL, które może mieć wartość PRAWDA, FAŁSZ lub (ze względu na logikę potrójną SQL) NIEZNANE:

SELECT * FROM myTable WHERE myField = 'someValue'

-- yields record 1

Jeśli chcę uzyskać wszystkie inne rekordy , nie mogę po prostu zanegować wyrażenia

SELECT * FROM myTable WHERE NOT (myField = 'someValue')

-- yields only record 3

Wiem, dlaczego tak się dzieje (logika trójskładnikowa) i wiem, jak rozwiązać ten konkretny problem.

Wiem, że mogę po prostu użyć myField = 'someValue' AND NOT myField IS NULLi otrzymuję wyrażenie „odwracalne”, które nigdy nie daje NIEZNANE:

SELECT * FROM myTable WHERE NOT (myField = 'someValue' AND myField IS NOT NULL)

-- yields records 2 and 3, hooray!

Ogólna sprawa

Porozmawiajmy teraz o ogólnym przypadku. Powiedzmy, że zamiast myField = 'someValue'mam złożone wyrażenie obejmujące wiele pól i warunków, być może podkwerend:

SELECT * FROM myTable WHERE ...some complex Boolean expression...

Czy istnieje ogólny sposób na „odwrócenie” tej wyprawy? Punkty bonusowe, jeśli działa w przypadku podwyrażeń:

SELECT * FROM myTable 
 WHERE ...some expression which stays... 
   AND ...some expression which I might want to invert...

Muszę wesprzeć SQL Server 2008-2014, ale jeśli istnieje eleganckie rozwiązanie wymagające nowszej wersji niż 2008, to też chcę o tym usłyszeć.

Możesz zawrzeć warunek w wyrażeniu CASE, które zwraca wynik binarny, na przykład 1 lub 0:

SELECT
  ...
FROM
  ...
WHERE
  CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1
;

Negowanie wyrażenia da ci wszystkie pozostałe wiersze z tego samego źródła danych, w tym te, w których someColumn ma wartość null:

SELECT
  ...
FROM
  ...
WHERE
  NOT CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1
  -- or: CASE WHEN someColumn = someValue THEN 1 ELSE 0 END <> 1
;

Od SQL Server 2012 masz również funkcję IIF , która jest po prostu otuleniem binarnego CASE jak wyżej. Więc to wyrażenie CASE:

CASE WHEN someColumn = someValue THEN 1 ELSE 0 END

będzie wyglądać tak, jeśli został przepisany przy użyciu IIF:

IIF(someColumn = someValue, 1, 0)

I możesz go użyć dokładnie tak samo, jak wyrażenie CASE. Nie będzie różnicy w wydajności, tylko kod będzie nieco bardziej zwięzły, być może również w ten sposób czystszy.

Pierwsza myśl, która przychodzi mi do głowy:

DECLARE @T AS table (c1 integer NULL);

INSERT @T (c1)
VALUES (1), (NULL), (2);

-- Original expression c1 = 1
SELECT T.c1
FROM @T AS T
WHERE c1 = 1;

Zwroty:

-- Negated
SELECT T.c1
FROM @T AS T
WHERE NOT EXISTS (SELECT 1 WHERE c1 = 1);

Zwroty:

Zależy to od sposobu, w jaki EXISTSzawsze zwraca true lub false , nigdy nieznany . Konieczność SELECT 1 WHEREjest niestety konieczna, ale może być wykonalna dla twojego wymagania, na przykład:

sql = "
    SELECT * 
    FROM someTable 
    WHERE " + someExpression + 
    " AND NOT EXISTS (SELECT 1 WHERE " + 
    someOtherExpression + ")";
result = executeAndShow(sql);

Zobacz EXISTS (Transact-SQL)

Nieco bardziej skomplikowane obrobionych przykład pokazujący jak albo EXISTSczy CASE/IIFmetody mogą być stosowane do odwrócenia indywidualnych predykatów:

DECLARE @T AS table 
(
    c1 integer NULL,
    c2 integer NULL,
    c3 integer NULL
);

INSERT @T 
    (c1, c2, c3)
VALUES 
    (1, NULL, 2),
    (2, 2, 3),
    (NULL, 1, 4);

Kod:

-- Original
SELECT 
    T.c1,
    T.c2,
    T.c3
FROM @T AS T
WHERE
    1 = 1
    -- Predicate #1
    AND T.c1 = 2
    -- Predicate #2
    AND T.c2 =
    (
        SELECT MAX(T2.c2)
        FROM @T AS T2
        WHERE T2.c2 IS NOT NULL
    )
    -- Predicate #3
    AND T.c3 IN (3, 4)
    ;

-- Invert predicates #1 and #2
SELECT 
    T.c1,
    T.c2,
    T.c3
FROM @T AS T
WHERE
    1 = 1
    AND NOT EXISTS (SELECT 1 WHERE 1 = 1
        -- Predicate #1
        AND T.c1 = 2)
    AND NOT EXISTS (SELECT 1 WHERE 1 = 1
        -- Predicate #2
            AND T.c2 =
            (
                SELECT MAX(T2.c2)
                FROM @T AS T2
                WHERE T2.c2 IS NOT NULL
            ))
    -- Predicate #3
    AND T.c3 IN (3, 4)
    ;

Jeśli nie masz nic przeciwko przepisywaniu podwyrażeń z góry, możesz użyć COALESCE:

SELECT *
FROM myTable
WHERE NOT (COALESCE(myField, 'notSomeValue') = 'someValue')

Państwo musi upewnić się, że 'notSomeValue'różni się od 'someValue'; najlepiej byłaby to jakaś całkowicie nielegalna wartość dla kolumny. (Oczywiście nie może być NULL.) Łatwo to zanegować, nawet jeśli masz długą listę:

SELECT *
FROM myTable
WHERE NOT (
    COALESCE(myField, 'notSomeValue') = 'someValue' AND
    COALESCE(myField2, 'notSomeValue') = 'someValue2' AND
    COALESCE(myField3, 'notSomeValue') = 'someValue3' AND
    COALESCE(myField4, 'notSomeValue') = 'someValue4'
)

Czystsze, prostsze i bardziej oczywiste niż CASElub IIF, moim zdaniem,. Głównym minusem jest druga wartość, o której wiesz, że nie jest równa, ale to naprawdę problem, jeśli nie znasz rzeczywistej wartości z góry. W takim przypadku możesz zrobić to, co sugeruje Hanno Binder i użyć COALESCE(myField, CONCAT('not', 'someValue')) = 'someValue'(gdzie 'someValue'tak naprawdę byłby sparametryzowany).

COALESCE udokumentowano, że będzie dostępny począwszy od SQL Server 2005.

Należy pamiętać, że takie bałaganie w zapytaniu (przy użyciu dowolnej z zalecanych tutaj metod) może utrudnić zoptymalizowanie zapytania przez bazę danych. W przypadku dużych zestawów danych IS NULLprawdopodobnie łatwiej jest zoptymalizować wersję.

Istnieje wbudowany operator zestawu EXCEPT , który skutecznie usuwa wyniki drugiego zapytania z pierwszego zapytania.

select * from table
except
select * from table
where <really complex predicates>

Czy dostępny jest COALESCE?

SELECT * FROM myTable WHERE NOT COALESCE(myField = 'someValue', FALSE)