SQL subquery's impact on overall performance

pzozulka
pzozulka used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Architect
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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 :-).

Author

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?
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial