[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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

0
pzozulka
Asked:
pzozulka
3 Solutions
 
Jim HornMicrosoft 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 PletcherSenior 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
 
DrewKjellCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now