ON vs where in exists clause sqlserver 2005

  • Dear all,

    table2 has one to many rel with table1 and table3

    table1 (table1Id int,table2Id int,col3 int)

    table2(table2Id int,col2 int,col3 int)

    table3(table3Id,table2Id int,col3 int)

    --are both correct if yes then which one is better? i checked both are good as long as join is used.

    1) select t1.col1,ti.col2

    from table1 t1 join table2 t2 on t1.table2Id =t2.table2Id

    where not exists

    (select t3.table3Id

    from table3 t3 join table2 t22 on t3.table2Id =t22.table2Id and t1.col3=t3.col3 and t2.table2Id =t22.table2Id )

    or

    select t1.col1,ti.col2

    from table1 t1 join table2 t2 on t1.table2Id =t2.table2Id

    where not exists

    (select t3.table3Id

    from table3 t3 join table2 t22 on t3.table2Id =t22.table2Id

    where t1.col3=t3.col3 and t2.table2Id =t22.table2Id )

    yours sincerely

  • You may want to look up Gail's article on IN vs EXISTS[/url]

  • Check following blog post to get differences b/w IN, NOT IN and EXISTS.

    Link: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/%5B/url%5D

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply