>>>>Hi,
>>>>
>>>>In order to make sure that my SQL Select will work (different from the one I use here as an example) I need to understand how sub-queries work.
>>>>
>>>>Here is a sample SQL Select:
>>>>
>>>>select fk_fld, flda, fldb from Table1 where fk_fld IN (select pk_fld from Table2 where <somecondition>
>>>> group by pk_fld)
>>>>
>>>>
>>>>My question is, does VFP (or SQL server) when executing the above SQL Select executes the sub-query first (one time) and then executes main query checking every record of Table1 to see if value FK_FLD is in sub-query?
>>>>
>>>>TIA.
>>>
>>>Yes, this is how it's executed. I prefer using EXISTS subquery, though, instead of IN/NOT IN. It may perform faster than IN subquery.
>>
>>Thank you. Speed is not that critical to me (in this case). But my SQL Select also has the OR clause and I wanted to be sure that the OR clause will only fire if the records is not in the sub-query. Here is a closer sample of my final query:
>>
>>
>>select fk_fld, flda, fldb from Table1 where fk_fld IN (select pk_fld from Table2 where <somecondition>
>> group by pk_fld) OR Table1.pk_field = nPkVariable
>>
>>
>>And I want to make sure that the resulting query will have a unique value of FK_FLD in every record.
>
>That's not correct. In SQL Server (not sure about VFP) there is no short-cuts which exist in many other languages. So, SQL Server may evaluate both conditions and it's not going to stop right away when one of the conditions is true.
>
>See blog referenced in
MSDN thread (by Gert-Jan) for details.
>
>Here is link for your convenience
http://www.xs4all.nl/~gertjans/sql/example6/shortcutting-predicate-evaluation.htmlThank you for the link (I will read it). But what I am thinking is even both sides of the OR are evaluated it still should work for me. I think I am going try to create a sample to test this ...
"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