Where clause in joins vs Where clause in Sub Query
Posted
by
Kanavi
on Stack Overflow
See other posts from Stack Overflow
or by Kanavi
Published on 2012-07-01T09:13:25Z
Indexed on
2012/07/01
9:15 UTC
Read the original article
Hit count: 342
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?
© Stack Overflow or respective owner