>>>>Thank you. I am trying to also understand why the EXISTS needs the expression T1.PK_FLD = T2.PK_FLD in subquery and according to Naomi the WHERE IN does not need it. But your use of 1 should make the subquery run faster.
>>>>
>>>>PS. If this is a duplicate post, I apologize. UT just crashed on me.
>>>>
>>>
>>>It crashed for me too today a few times.
>>>
>>>As I said in my other message, there is absolutely no difference in using EXISTS (select 1 ...)
>>>or EXISTS (select * ...)
>>>
>>>In WHERE IN we don't need an expression because IN actually selects all the values. The EXISTS subquery just tests and exits as soon as it finds a value satisfying the condition. So EXISTS may perform faster than IN in addition of possible problems associated with IN which you don't have when using EXISTS.
>>
>>But if you remove T1.PK_FLD = T2.PK_FLD from the subquery, in case of the WHERE IN, doesn't subquery then select ALL records from Table2 and not just those that have value of PK_FLD equal to the PK_FLD in the Table1?
>
>The whole purpose of IN is to check one value against another. Same case with EXISTS.
>
>The way you wrote it is essentially the same as also do an INNER JOIN. I think we may better use EXISTS to avoid any ambiguity.
No. INNER JOIN would select more than 1 record, in case of one-to-many. And forget about EXISTS for now. I am just trying to see why IN does not need the T1.PK_FLD = T2.PK_FLD in the subquery. For example, I used in my example columns that have the same name but there could be a case where columns have different names. For example:
select * from Table1 T! where COL1 in (select COL2 from Table2 T2 where COL3='123')
Would in the above case you still not use the T1.PK_FLD = T2.PK_FLD?
"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