The difference in these two queries (EXISTS with NOT LIKE and NOT EXISTS with LIKE)

Question

I have a really hard time to understand the difference between these two queries. The don't give the same output, just a slightly difference in the results. Isn't these two queries the same, but just inverted? You can see the difference in the last subquery.

SELECT *
FROM[dbo].[MNO International AB$Item] t1
WHERE 
    t1.[Item Type] = 0
    AND (
            NOT EXISTS(SELECT * FROM [dbo].[MNO International AB$Item Cross Reference] t2 WHERE t2.[Item No_] = t1.No_ AND t2.[Cross-Reference Type No_] = 'EAN13' AND t2.[Cross-Reference No_] <> '' AND t2.[Cross-Reference Type] = 3)
            OR t1.[Statistics Group] = 0
        ) --Result: 2178

    AND NOT EXISTS 
                (SELECT * FROM [dbo].[MNO International AB$Master] t2 
                 WHERE t1.[Master No_] = t2.No_ 
                 AND t2.[Collection No_] LIKE 'NEW-NOS'
                 )

The other Query

SELECT *
FROM[dbo].[MNO International AB$Item] t1
WHERE 
    t1.[Item Type] = 0
    AND (
            NOT EXISTS(SELECT * FROM [dbo].[MNO International AB$Item Cross Reference] t2 WHERE t2.[Item No_] = t1.No_ AND t2.[Cross-Reference Type No_] = 'EAN13' AND t2.[Cross-Reference No_] <> '' AND t2.[Cross-Reference Type] = 3)
            OR t1.[Statistics Group] = 0
        ) --Result: 2178

    AND EXISTS 
                (SELECT * FROM [dbo].[MNO International AB$Master] t2 
                 WHERE t1.[Master No_] = t2.No_ 
                 AND t2.[Collection No_] NOT LIKE 'NEW-NOS'
                 )

"The don't give the same output" - nor should you expect them to. It may be a bit mathy, but look at the [Negation section on Existential Quantification on Wikipedia](https://en.wikipedia.org/wiki/Existential_quantification#Negation). Query one is "there are *no* items like `NEW-NOS`", Query two is "there is *at least one* item that *isn't* like `NEW-NOS`" — Damien_The_Unbeliever, Oct 26 '15 at 09:20
The second query includes a "not like" clause instead of a "Like", so it isnt quite the inversion. If the clause was "Like" then the second one would be an inversion of the first. — Takarii, Oct 26 '15 at 09:21
Bear in mind that NULLs will affect this. `NULL NOT LIKE 'NEW-NOS'` and `NULL LIKE 'NEW-NOS'` are both unknown, which will evaluate the same as a false value in a predicate. — Matt Gibson, Oct 26 '15 at 10:13
No they are not the same. Presence of something else is not the same as not the presence of something. — paparazzo, Oct 26 '15 at 11:25

score 2 · Accepted Answer · answered Oct 26 '15 at 09:38

2

I think results will give not same. Because:

NOT(A = B AND C <> D)
= NOT(A = B) OR NOT(C <> D)
= A <> B OR C = D

Return your query:

NOT EXISTS (t1.[Master No_] = t2.No_ AND t2.[Collection No_] NOT LIKE 'NEW-NOS')
= EXISTS (t1.[Master No_] <> t2.No_ OR t2.[Collection No_] LIKE 'NEW-NOS')

answered Oct 26 '15 at 09:38

Nguyễn Hải Triều

1,454
1
8
14

Easiest example to illustrate the difference. What happens when MNO International AB$Master is empty? – Taemyr Oct 26 '15 at 09:42
If MNO International AB$Master is empty, the second-subquery in where always return NULL. NOT EXISTS (SELECT NULL) --> always return TRUE EXISTS (SELECT NULL) --> always return FALSE – Nguyễn Hải Triều Oct 26 '15 at 09:46
Yes, hence it serves as an easy example. – Taemyr Oct 26 '15 at 09:51
Thank you for the explanation :) – Sadjad Johansson Nov 03 '15 at 12:40

The difference in these two queries (EXISTS with NOT LIKE and NOT EXISTS with LIKE)

1 Answers1