Sergey,
Thanks for your suggestions. I am a SQL novice, so sometimes I don't even know (or remember) some of the basic language syntax. Your response helped me a lot.
David
>David,
>
>
>How do I measure query efficiency?>
>That's what execution plans and tests are for.
>
>
>Often, there are multiple ways of writing a single query. For example,
>>
>>SELECT Table1.Field1
>>FROM Table1
>> INNER JOIN Table2
>> ON Table1.PK1 = Table2.FK2
>>WHERE Table2.Field2 = value1
>>
>>could be written,
>>
>>SELECT Table1.Field1
>>FROM Table1
>> INNER JOIN
>> (SELECT Table2.Field2, Table2.FK1
>> FROM Table2
>> WHERE Table2.Field2 = value1)
>> ON Table1.PK1 = Table2.FK1
>>
>>or,
>>
>>SELECT Table1.Field1
>>FROM Table1
>>WHERE Table1.PK1 IN
>> (SELECT Table2.FK1
>> FROM Table2
>> WHERE Table2.Field2 = value1)
>>
>>Are any of these more efficient than the others?>
>In my experience, the first one and following one are most efficient but, again, analyze execution plans an run tests.
SELECT Table1.Field1 FROM Table1
>WHERE EXISTS (SELECT * FROM Table2
> WHERE Table1.PK1 = Table2.FK2
> AND Table2.Field2 = value1)
>
>
>
>The type of query that I am having the most trouble with is when I want to select the rows in a table that don't have their primary keys listed as a foreign key in a table with which it has a 0..* relationship. I have been coding it as,
>>
>>SELECT Table1.Field1
>>FROM Table1
>> LEFT OUTER JOIN Table2
>> ON Table1.PK1 = Table2.PK2
>>WHERE Table2.FK1 IS NULL
>>
>>Is there a more efficient way to write this query? Microsoft SQL doesn't support MINUS, which would be my first inclination.>
>I would first try EXISTS query
SELECT Table1.Field1 FROM Table1
> WHERE NOT EXISTS (SELECT * FROM Table2
> WHERE Table1.PK1 = Table2.FK2)
>
David S. Alexander
Kettley Publishing
20271 SW Birch Street, 2nd Floor
Newport Beach, CA 92660-1752