Alternative except SQL Server

Question

I want to get the results from the following table. Get all IDs and treetypes, with the exception of Apple with Id = 102.

Id         TreeType 
---------------------
99         Apple
99         Mango   
102        Orange  
101        Blackberry   
102        Apple

The result will be.

Id         TreeType 
---------------------
99         Apple
99         Mango   
102        Orange  
101        Blackberry   

One way to get the result from the following query.

select id, TreeType
from x

except

select id, TreeType
from x
where id = 102 and TreeType = 'Apple'

Is it possible to get the result from a single select statement?

I just want to avoid it, because in a real scenario it becomes very expensive for me.

Answer 1

How about just

SELECT id,TreeType
FROM x
WHERE NOT(id = 102 AND TreeType = 'Apple')

At first it touched me - I wanted to think what WHERE (id <> 102 AND TreeType <> 'Apple')would work, but it is not. This makes sense when you rethink it as a bools table - in this case it will be

id    opr.    TreeType
======================
true  AND     false    -- 99 apple: false
true  AND     true     -- 99 mango: true
true  AND     false    -- 102 orange: false
true  AND     true     -- 101 blackberry: true
false AND     true     -- 102 apple: false

, , , , NOT.

id    opr.    TreeType
======================
false AND     false     -- 99 apple: not(false) = true
false AND     false     -- 99 mango: not(false) = true
true  AND     false     -- 102 orange: not(false) = true
false AND     true      -- 101 blackberry: not(false) = true
true  AND     true      -- 102 apple: not(true) = false

Answer 2

NOT EXISTS -

SELECT id,TreeType
FROM x t
WHERE NOT EXISTS 
(
    SELECT 1 FROM x 
    WHERE 
        id = t.id AND TreeType = t.TreeType 
        AND id = 102 
        AND TreeType = 'Apple'
)