Howto use joins instead of subqueries?

[TH]

Hi,

Trying to get a grip on the "join" thing :)
Up until now, I allways used this kinda method:

"(select t1.a from t1 where t1.b in (select t2.ab from t2 where t2.b=0))"

How can this be accomplished using joins? And if you have the time please
explain the "bits" :)

Thank you.

--�
TH

[Ray Higdon]

select t1.a from t1 where t1.b in (select t2.ab from t2 where t2.b=0

Using joins it would be something like this

select t1.a from t1 join t2 on t1.b = t2.ab
where t2.b = 0

This would assume an inner join so all that would be returned would be
t1.a where there was a match between t1.b and t2.ab.

Example: If I created two simple tables:

create table table1 (ikey int)
create table table2 (ikey int)

and populated them

insert into table1 select '55' union all select '56' union all select
'57'
insert into table2 select '55' union all select '56' union all select
'57' union all select '58'

Now I can write some join statements:

select a.ikey from table1 a join table2 b on a.ikey = b.ikey

notice I am able to use an alias for the table by using it in the from
statement (NOTE: if you use an alias you have to be consistent, you can
not refer back to the table name elsewhere in the query)

You will note that the above query only returned 3 rows, not the fourth
row we inserted into table2 becuase there was no match in table1. If you
wanted to see all values in table2 you could write a right join:

select a.ikey as Tbl1_Key, b.ikey as Tbl2_Key
from table1 a right join table2 b on a.ikey = b.ikey

This will return all matching rows and any rows in table 2 (because it
is on your right in the from clause) You will see a null value for the
'58' for table 2 as there is no match in table 1.

To get more thorough explanation of joins, check out BOL (books-online)
documentation that comes with SQL Server.

HTH
Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[TH]

Very nice description.
Thanks!

--
TH
Pffmppppmppffpmmpp fmpmfpmpp mpmppfmfm
mmpmppmpfppfpffmpp pffmpppfmpmfffmmffpppmfm...
<http://www.namesuppressed.com/kenny/>

[Geroge S. Z]

JOIN has better performance than sub-query.

For your instance, you can get even better performance than JOIN if you
query like this:

select t1.a from t1
where exists ( select t2.ab from t2 where t2.b=0 and t2.ab = t1.b )

if t1 and t2 are very large tables, you will benifit a lot. Otherwise,
forget it, no difference.

George S. Z

"TH" <th_at_cogito.greatdanedk> wrote in message
news:3f***********************@dtext02.news.tele.d k...

Very nice description.
Thanks!

--
TH
Pffmppppmppffpmmpp fmpmfpmpp mpmppfmfm
mmpmppmpfppfpffmpp pffmpppfmpmfffmmffpppmfm...
<http://www.namesuppressed.com/kenny/>