0

Table A

Column B : AA,BB,CC,DD,EE,FF

Table C

Column D : AA,BB,CC,GG,KK,MM

I need data from Table A which is not Present in Table C

Output Should be : DD,EE,FF

Tried Following :

Select A.B from A
Left Join C on A.B = C.D
where A.B not in(Select C from D)
GMB
  • 216,147
  • 25
  • 84
  • 135
bHaRaTh N
  • 1
  • 1
  • 8

3 Answers3

3

just don't do the join

select B
from A
where B not in(Select C from D)

You were trying to make it to complicated

Hogan
  • 69,564
  • 10
  • 76
  • 117
2

not exists springs to mind: it might be more efficient than not in, and it is null-safe, while not in is not (if any of the value returned by the not in subquery is null, all rows in the outer query will be returned, which is presumably not what you want):

select a.*
from a
where not exists (select 1 from c where a.b = c.d)

Or, in the spirit of your original query, you can go for the anti-left join:

select a.*
from a
left join c on a.b = c.d
where c.d is null
GMB
  • 216,147
  • 25
  • 84
  • 135
0

This is the definition of an anti-join:

select A.B 
from A
left join C on A.B = C.D
where C.D is null
The Impaler
  • 45,731
  • 9
  • 39
  • 76