August 20, 2009 at 1:54 pm
Hi,
Am getting a confusion between the INNER JOIN and Correlated subquery.
I can use ethr ways.
In what particular scenarios can i use them. Am not finding a mush difference between them while using them.
RM
August 20, 2009 at 1:56 pm
sorry to post two times. I encountered error while posting the earlier one!
August 20, 2009 at 3:15 pm
There are certainly some similarities, but there are also differences. The big difference is that a correlated subquery can generally only return one value (one row with one column) whereas a JOIN can return any number of columns and rows. A correlated subquery returns NULL if there are no records that meet the criteria, so, in that sense, it is more like an OUTER JOIN rather than an INNER JOIN.
There are some cases where a correlated subquery can return more than one value. The first is when used in conjunction with an EXISTS or NOT EXISTS. These are both more efficient than using JOINS, because SQL can return a result as soon as it finds any record that meets the criteria for the (NOT) EXISTS, but needs to return all rows that meet the criteria with the JOIN.
The other case where a correlated subquery can return more than one value is when used with the keywords ALL or ANY. These are not used very often, because you can usually rewrite them using (NOT) EXISTS, which is more efficient. With ANY or ALL, the subquery can only return one column, but can return any number of rows.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 20, 2009 at 3:30 pm
If you have to retrieve values from a table, rather than simply test for existence, you probably should use a JOIN. Joins and correlated subqueries produce different execution plans and the JOIN is more efficient at retrieving one or more values than multiple correlated subqueries.
declare @dbs table (dbname varchar(50) primary key)
insert into @dbs
select 'Model' union all
select 'Msdb' union all
select 'Master'
set statistics io on;
set statistics time on;
select dbname
,(select create_date from sys.databases where name = dbname) as create_date
,(select collation_name from sys.databases where name = dbname) as collation_name
,(select state_desc from sys.databases where name = dbname) as state_desc
,(select user_access_desc from sys.databases where name = dbname) as user_access_desc
from @dbs d
set statistics time off;
set statistics io off;
print '-------------------'
set statistics io on;
set statistics time on;
select dbname, create_date, collation_name,state_desc,user_access_desc
from @dbs d
join sys.databases s on s.name = d.dbname
set statistics time off;
set statistics io off;
If you look at the statistics from the above example, the correlated subquery requires three scans and 24 logical reads of the sysdbreg table to produce the same results which the join accomplishes with no scans and only 12 logical reads.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 7:35 pm
Thank u.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply