Joins or Subqueries?

[THoey]

All,

I am constantly seeing queries posted here that to me seem way more complex due to using joins when it seems a subquery would work. Is there any benefit to using a join over a subquery or does it get translated the same way?

Still learning all the time... Terry M. Hoey

 

[Databaseguy]

If you've got 3 tables, with many to one to many joins, then you're query is not updatable. However, it's pretty easy to make changes.

Using sub-queries can overcome a lot of limitations. However, when someone wants to make a change, it can be a nightmare figuring out where the data comes from and what impact the change will have.

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[MichaelRed]

IMHO, it is mostly a matter of learning order. Like the controls on airplanes. Long away and far ago, someone figured out that a LOT of 'pilot error' was just reaching for the wrong control in a moment of stress. The great nebolous they also (eventually) figured out that many (actually MOST) of these errors were where the pilot reached for the 'correct' control - or at least where it would have been if he were in the airplane which he learned to fly in. So, we all appear to 'revert' to the way we learned it when we are in any kind of hurry (stress?). In the instance of programming, if you learned Joins (VASTLY FAVORED by Ms. Access), you will probably favor the Joins. If you learned sub-queries in a different environment, you will probably favor them.

On the more technical level, sub-queries do yield a bit more precision on the order of operations than Joins. If this is an issue, sub-queries are not just better, they are necessary.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over