how to convert sql query containing "NOT IN" clause to joins

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  July 26, 2006 at 8:30 am

  #113710

  Hi,

  I am trying to convert queries which include NOT IN clause so that they can be done using JOINS. But I am not knowing how to do that. Lets say I have a query like this

  SELECT Col1 FROM table1

  WHERE Col1 NOT IN (SELECT Col2 FROM table2 WHERE col1 = @Col1)

  please let me know.

  Thanks,

  Sridhar.

• apinnelli

  Mr or Mrs. 500

  Points: 525

  More actions

  July 26, 2006 at 8:41 am

  #651649

  Hi, try this

  declare @a table (x int null)

  insert into @a(x) values (1)

  insert into @a(x) values (2)

  insert into @a(x) values (3)

  insert into @a(x) values (4)

  insert into @a(x) values (5)

  insert into @a(x) values (6)

  insert into @a(x) values (7)

  declare @b-2 table (x int null)

  insert into @b-2(x) values (1)

  insert into @b-2(x) values (3)

  select * from @a aa left outer join @b-2 bb on aa.x = bb.x where bb.x is null

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  July 26, 2006 at 8:48 am

  #651653

  Thank you.