How to change a subquery to a join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

How to change a subquery to a join?

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-07-02 : 17:56:44

Hi,

How do I change to use a join instead of a subquery?
Can you please explain the thought process.

Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-02 : 18:10:55

By tweaking the join syntax, you can do this:

SELECT p.country
FROM population p INNER JOIN population p2 ON p.country = p2.country AND p.population > p2.population
where p2.yr = 1970

But I don't think this will help performance, if that's what you're after.

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-07-02 : 21:55:03

You missed a bit... the filter for 1960.

SELECT p.country
FROM population p INNER JOIN population p2 ON p.country = p2.country AND p.population > p2.population
where p2.yr = 1970
and p1.yr = 1960

Here's a tip: give more useful aliases.

SELECT p1960.country
FROM population p1960
INNER JOIN
population p1970
ON p1960.country = p1970.country
AND p1960.population > p1970.population
where p1970.yr = 1970
and p1960.yr = 1960

Personally though, I'd tend to arrange it like this:

SELECT p1960.country
FROM population p1960
INNER JOIN
population p1970
ON p1960.country = p1970.country
AND p1970.yr = 1970
AND p1960.yr = 1960
WHERE p1960.population > p1970.population

...because I see the year filters as more like join clauses, and the population comparison as more like a result filter.

Rob Farley
http://robfarley.blogspot.com

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-07-03 : 05:21:55

Thanks for that everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources