SQL subquery's impact on overall performance

What's the difference in performance of the following two queries?

Query 1
SELECT *
FROM Table1 T1 JOIN Table2 T2 ON T1.Id = T2.Id
WHERE T1.Age > 25
AND ...
AND ...
AND T1.Id NOT IN (Select T3.Id FROM Table3 T3 WHERE T3.Id = T1.Id)

Open in new window


Query 2
SELECT *
FROM Table1 T1 JOIN Table2 T2 ON T1.Id = T2.Id
WHERE T1.Age > 25
AND ...
AND ...
AND T1.Id NOT IN (Select T3.Id FROM Table3 T3)

Open in new window

LVL 8
pzozulkaAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Query1 is a correlated subquery, where the subquery is executed ONCE FOR EACH ROW in the main query, so it will execute much slower.

Query 2 is an IN clause with a subquery that returns a simple list, and executes only once.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The first query's condition can never be true, since t1.id is basically being NOT'd to itself,
so no rows can ever be returned, so presumably it will run quickly anyway :-).
0
 
pzozulkaAuthor Commented:
What about query 2, is there another way to write it to increase performance. Perhaps somehow eliminating the subquery, and instead moving it to the FROM clause? If so, how would you re-write it?
0
 
DrewKjellConnect With a Mentor Commented:
Something like this:
SELECT *
FROM Table1 T1 JOIN Table2 T2 ON T1.Id = T2.Id
LEFT OUTER JOIN Table3 T3 on T3.Id = T1.Id
WHERE T1.Age > 25
AND ...
AND ...
AND T3.ID IS NULL

Open in new window

0
All Courses

From novice to tech pro — start learning today.