CODEWITHSUNDEEP
sql
Pratik Sarangi
Pratik Sarangi

Reputation: 3

How to use NOT IN with the Union Result

I am trying to get the distinct values from one table having different conditions.

The Query that I am using is

Select A.* 
from A
 where A.x not in (select B.x from B 
                   union
                   select C.x from C
                  )

Please note that I have been using CTE to get the different values.

When I standalone run this select B.x from B union select C.x from C
I see the output. Same goes for table A also.

But when I run it together I see no data.

Upvotes: 0

Views: 101

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42753

One way is to use not exists

Select A.* from A where 
not exists (select 1 from b where b.x = A.x)
and
not exists (select 1 from c where c.x = A.x)

Upvotes: 2

yusuf hayırsever
yusuf hayırsever

Reputation: 701

;with tabletemporary
AS 
( 
    SELECT b.x AS name from b UNION SELECT c.x AS name FROM c
)
Select a.* FROM a WHERE a.x NOT IN
          (SELECT name FROM tabletemporary)

you can use this code

Upvotes: 0

Related Questions