Performance benefits between a JOIN statement vs a nested SELECT

Is there a performance bonus between these 2 sql statements?
Using JOIN:
SELECT * FROM A, B WHERE A.ID = B.ID;
Using nested SELECT:
SELECT * FROM A WHERE A.ID = (SELECT ID FROM B);
ThanksThere may be only a small amount of difference, but these two statements are
really very different. The join will return data from both A and B, meaning
that the entire row will be needed from B, instead of just the ID column.
And unless there is a 1 to 1 relationship between A and B, you are going to
get different results.
Performancewise, the second should be faster (and could be faster still
probably if you rewrote it using exists)
select *
from A
where exists (select *
from B
where a.id = b.id)
But the bottom line is that you are dealing with two different queries that
are not equivalent. So depending on the question you are trying to answer,
who cares which is fastest, it is which gives you the correct answer.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"cheem" <cheem@.msn.com> wrote in message
news:%23i7qoxVGFHA.1836@.tk2msftngp13.phx.gbl...
> Is there a performance bonus between these 2 sql statements?
> Using JOIN:
> SELECT * FROM A, B WHERE A.ID = B.ID;
>
> Using nested SELECT:
> SELECT * FROM A WHERE A.ID = (SELECT ID FROM B);
> Thanks
>|||Otherwise you can use
Select * from A where id in (Select id from B)
Madhivanan
Louis Davidson wrote:
> There may be only a small amount of difference, but these two
statements are
> really very different. The join will return data from both A and B,
meaning
> that the entire row will be needed from B, instead of just the ID
column.
> And unless there is a 1 to 1 relationship between A and B, you are
going to
> get different results.
> Performancewise, the second should be faster (and could be faster
still
> probably if you rewrote it using exists)
> select *
> from A
> where exists (select *
> from B
> where a.id = b.id)
> But the bottom line is that you are dealing with two different
queries that
> are not equivalent. So depending on the question you are trying to
answer,
> who cares which is fastest, it is which gives you the correct answer.
> --
>
----
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested
in
> consulting services. All other replies may be ignored :)
> "cheem" <cheem@.msn.com> wrote in message
> news:%23i7qoxVGFHA.1836@.tk2msftngp13.phx.gbl...|||cheem
Be aware if your id is null in the #b table you don't get a right output .
Look at below example
create table #a
(
[id] int null
)
insert into #a values (1)
insert into #a values (2)
create table #b
(
[id] int null
)
insert into #b values (null)
insert into #b values (2)
select * from #a where [id] not in (select [id]from #b)--Wrong one
Always USE
select * from #a where [id] not in (select [id]from #b where #a.id=#b.id)
drop table #a,#b
"cheem" <cheem@.msn.com> wrote in message
news:%23i7qoxVGFHA.1836@.tk2msftngp13.phx.gbl...
> Is there a performance bonus between these 2 sql statements?
> Using JOIN:
> SELECT * FROM A, B WHERE A.ID = B.ID;
>
> Using nested SELECT:
> SELECT * FROM A WHERE A.ID = (SELECT ID FROM B);
> Thanks
>|||cheem,
If you are only interested in the columns values of table A, and if
there is at most one matching row in table B for each row in table A,
then there are basically three methods to achieve the same thing:
Inner Join
--
1a) SELECT A.* FROM A, B WHERE A.ID = B.ID
1b) SELECT A.* FROM A INNER JOIN B ON B.ID = A.ID
Exists
--
2) SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.ID = A.ID)
In
--
3) SELECT * FROM A WHERE ID IN (SELECT ID FROM B)
Which method is fastest depends on the relative table size, and the
number of distinct values in table B.
Method 1 has the advantage that the optimizer can use the MERGE JOIN
operator which is very efficient.
Method 2 has good performance overall, and particularly if table (or
selection of) table A is relatively small compared to table B
Method 3 is very fast if table B is just a few unique values. If you use
method 3, then make sure B.ID never contains NULL.
So as usual: it depends.
Hope this helps,
Gert-Jan
cheem wrote:
> Is there a performance bonus between these 2 sql statements?
> Using JOIN:
> SELECT * FROM A, B WHERE A.ID = B.ID;
> Using nested SELECT:
> SELECT * FROM A WHERE A.ID = (SELECT ID FROM B);
> Thanks