Where clause in joins vs Where clause in Sub Query
- by Kanavi
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?
