>>>I prefer using EXISTS because of the possible NULL problem with IN syntax.
>>>
>>>BTW, your IN is not correct. You don't need to use correlated fields in IN condition, e.g. this would be correct:
>>>
>>>
>>>select Table1.* T1 where T1.pk_fld in (select pk_fld from Table2 T2 where T2.fld2 = '123')
>>>
>>
>>Thank you.
>>
>>In terms of correcting my syntax, are you saying that the derived query will automatically only use records of PK_FLD equal to PK_FLD in Table1?
>
>Yes.
>
>BTW, please check this blog
>
>
http://sqlservercode.blogspot.com/2007/04/you-should-never-use-in-in-sql-to-join.html>
>so you may stop using IN (..) syntax after reading it. I know I did switched long enough to NOT EXISTS /EXISTS .
The blog has 2 problems (IMHO) 1. The problem described is based on typing error or presence of NULL. But if you don't make a typing error or know that PK cannot have NULLs, no problem. 2. The subquery has SELECT * which is probably slower than using SELECT JustOneColumn. But of course, everybody has an opinion and can use whatever they want.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
