CODEWITHSUNDEEP
sqlsql-server
Abrar Ahmad
Abrar Ahmad

Reputation: 165

Alternate of Except in SQL Server

I want to get results from the following table. Get all ID and treetypes except Apple having Id=102.

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

Result will be.

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

One way we can get result from following query.

select id, TreeType
from x

except

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

It it possible to get the result from one select statement?

I just want to avoid because of on actual scenario it become very costly for me.

Upvotes: 4

Views: 586

Answers (2)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Another solution is using NOT EXISTS like this-

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'
)

Upvotes: 4

Dan Field
Dan Field

Reputation: 21641

How about just

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

This did trip me up at first though - I wanted to think WHERE (id <> 102 AND TreeType <> 'Apple') would work but it doesn't. This makes sense when you rethink it as a table of bools - in that case it'd 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

Using the above syntax, we get just the row we want to target to be true and use 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

Upvotes: 4

Related Questions