CODEWITHSUNDEEP
sql-serverquery-optimization
Chris
Chris

Reputation: 114

SQL Server Query Optimizing - WHERE clause vs SELECT subquery

When creating a query that joins 2 tables. I thought of 2 possible ways to write the query. Below is a simplified example. Could you please let me know which would be faster?

SELECT t1.a,
       t2.a
FROM   table1 t1
       JOIN table2 t2
         ON t1.b = t2.b
WHERE  t2.c = 'test'

OR

SELECT t1.a,
       t2.a
FROM   table1 t1
       JOIN (SELECT a, b
             FROM   table2
             WHERE  c = 'test') t2
         ON t1.b = t2.b

Upvotes: 1

Views: 484

Answers (3)

AnandPhadke
AnandPhadke

Reputation: 13506

if you have proper indexes on table1.id and table2.id columns then I think the first query will perform better.

Upvotes: 0

podiluska
podiluska

Reputation: 51514

It depends on your data, and your data structures.

You can find out for yourself by using the "Include Actual Execution Plan" option on the Query menu in SSMS.

I would say the first option is clearer though.

Additionally, the second query as written won't actually work.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 454000

They should give exactly the same plan.

SQL Server will easily transform one to the other. Checking the execution plan is the only way to know for sure though.

Upvotes: 5

Related Questions