>Hi,
>
>What is a better practice and more efficient when using a derived table in a SQL Select:
>
>
>select Table1.* T1 where exists (select * from Table2 T2 where T1.pk_fld = T2.pk_fld and T2.fld2 = '123')
>
>
>vs
>
>
>select Table1.* T1 where T1.pk_fld in (select pk_fld from Table2 T2 where T1.pk_fld = T2.pk_fld and T2.fld2 = '123')
>
>
>TIA
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')
If it's not broken, fix it until it is.
My Blog