CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005
Pankaj
Pankaj

Reputation: 10115

Where clause in joins vs Where clause in Sub Query

DDL

create table t
(
    id int Identity(1,1),
    nam varchar(100)
)


create table t1
(
    id int Identity(1,1),
    nam varchar(100)
)

DML

Insert into t( nam)values( 'a')
Insert into t( nam)values( 'b')
Insert into t( nam)values( 'c')
Insert into t( nam)values( 'd')
Insert into t( nam)values( 'e')
Insert into t( nam)values( 'f')


Insert into t1( nam)values( 'aa')
Insert into t1( nam)values( 'bb')
Insert into t1( nam)values( 'cc')
Insert into t1( nam)values( 'dd')
Insert into t1( nam)values( 'ee')
Insert into t1( nam)values( 'ff')

Query - 1

Select t.*, t1.* From t t
Inner join t1 t1 on t.id = t1.id
Where t.id = 1

Query 1 SQL profiler Result

Reads => 56, Duration => 4

Query - 2

Select T1.*, K.* from 
(
    Select id, nam from t Where id = 1
)K
Inner Join t1 T1 on T1.id = K.id

Query 2 SQL Profiler Results

Reads => 262 and Duration => 2

You can also see my SQlFiddle

Query - Which query should be used and why?

Upvotes: 2

Views: 218

Answers (1)

Ivan Golović
Ivan Golović

Reputation: 8832

Optimizer generates same execution plans with same total cost for both of the queries. You can view the cost metric by checking the "Include Actual Execution Plan" option in SSMS and then viewing the "Estimated Subtree Cost" of the first element for each of the queries displayed in graphical execution plan. You can also compare time metric and IO reads metric by enabling them using SET STATISTICS TIME ON and SET STATISTICS IO ON. Perform test several times and take the best average result for comparison. If tests show equal performance I would take the query which is more readable and easier to maintain.

Upvotes: 1

Related Questions