The two sql statement are as below:
1.
select Sessionid from tableTest1
where sessionid in
(
select sessionid from tableTest2
)
2.
select t1.Sessionid from tableTest1 t1,tableTest2 t2
where t1.Sessionid = t2.Sessionid
Because tableTest1 and tableTest2 are huge, so i am very
care the efficient of SQL Statement.
If you're in doubt about the performance of two similar queries, use the Query Analyzer tool's "Show Execution Plan" feature to see how SQL Server will perform them. SQL Server's physical operation is not tied to the syntax you use--functionally identical queries are generally executed the same way, even if they look different in T-SQL code.
I think in this case, performance will be the same, since both are basically limited by matching rows.
-Ryan / Kardax
select t1.SessionId from tableTest1 as t1
where exists(select * from tableTest2 as t2 where t2.SessionId = t1.SessionId);
In general, pick a construct that closely represents say the solution written in English language for example. So if you want to get only the SessionIds from t1 that exist in t2, then use EXISTS. There is no reason to use a JOIN/IN or any other construct. On the other hand, if you want to get SessionId from t1 and additional columns from t2, then a JOIN is more appropriate.
According what you said,i think that "in" can be replaced by "exists" in
any T-SQL code almost. I wonder which instances can be the most suitable
for "in" used in?