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: 348

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

Related posts about sql-server

Related posts about sql-server-2008