INNER JOIN vs Correlated Subquery !

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INNER JOIN vs Correlated Subquery !

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-01-05 : 02:11:02

Hi Pals,

Need some help in understanding the internal execution of the below 2 queries.
Theoritically and upto my knowledge , i know Co-related sub-queries takes more time to execute.

Can anybody explain the reason for this.

Is there any difference in comparing/joining the two tables.

-- Using INNER JOIN
select e.ename, d.dname from Emp e INNER JOIN Dept d ON e.deptno = d.deptno

-- Using Correlated Sub-query
select e.ename,
(select dname from dept where deptno=e.deptno)
from Emp e

-- Sample Tables
create table Dept
( deptno int primary key,
dname varchar(20)
)
insert into Dept values (10,'Accounts')
insert into Dept values(20,'Sales')
insert into Dept Values(30,'Marketing')
insert into Dept values(40,'Production')

create table Emp
(Empno int primary key,
Ename varchar(20),
deptno int
)
insert into Emp
select 101,'Manu',10
union all
select 102,'Anu',40
union all
select 103,'Phani',30
union all
select 104,'Sharath',20

Please suggest.
Thanks in Advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-05 : 07:25:41

The 2 queries are different and would give you different results. Try running the same with some sample data.