Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 23rd, 2007, 05:46 PM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default SUB QUERIES VS JOINS

Hi,

Could someone please explain to me what is better to use! A SubQuery or a Join? I normally use subqueries like this:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName
FROM Products

However, a friend told me that it is better to use JOIN.

1 - What is the difference?
2 - Which one is faster?
3 - In what situation should I choose one as opposed to the other?

Cheers,

CPALL
 
Old March 23rd, 2007, 05:55 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Generaly joins are more efficient and more standard sql code. Most people only use a Subquery when they can't use a join or in a where clause. Joins should be preffered and the way of joining in ANSI sql 92 way is preferred. In this way you will only have one table in the "from " part of the query and additional tables will be joing using Inner Join, Outer Join, Full Outer Join type syntax.


 
Old March 25th, 2007, 09:16 PM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

Hi robprell,

Thanks for your explanation. I will try to use more joins.
 
Old March 26th, 2007, 07:12 AM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anubhav.kumar
Default

Hi,

Furthur to this, when u use joins,the server creates a execution plan for the query based on indexes defined in the participating tables which enhances performance. If u use the subquery [say as a derived table in From clause] then the result is used as if a table scan is done while running the query.

Also, the example you have sited used correlated query which should be the last option while writing queries, bcse it runs for each record before giving results

Anubhav Kumar
 
Old March 26th, 2007, 10:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by anubhav.kumar
 Hi,

Furthur to this, when u use joins,the server creates a execution plan for the query based on indexes defined in the participating tables which enhances performance. If u use the subquery [say as a derived table in From clause] then the result is used as if a table scan is done while running the query.

Also, the example you have sited used correlated query which should be the last option while writing queries, bcse it runs for each record before giving results

Anubhav Kumar
Your first comment is simply not true. In fact, the optimizer, for many kinds of subqueries, will generate the exact same execution plan as it would for an inner join. It is also not true that derived tables always result in the creation of a temporary table where a table scan would be necessary. If the optimizer can materialize the derived table from an index it will do so. This is one reason why covering indexes can be so useful.

It's not always easy to know a priori when a subquery would be identical to a join in execution performance, so I agree with the sentiment expressed by other posters that all other things being equal, a JOIN is better than a subquery, if for no other reason than they are easier to read.

However, there are situations where the reverse is true, where a subquery can be "better" than a join and result in better performance (to be sure, there aren't many of these ;).

As well, there are some queries that cannot be expressed by anything other than a correlated subquery, so it can be a necessary tool.

All things being equal, INNER JOINs are preferable to subqueries, but many times not all things are so equal, so one should ALWAYS be wary of advice to always (or never) do anything... ;)

Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
 
Old March 26th, 2007, 03:25 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When deciding which to use I always argue to use the scientific method. Or basically try them both in similar situations and compare the time results. This as with just about all things has some exceptions depending on the exact combination of events in a particular situation. But I think all posters agreed, start with inner joins unless you find a good reason not to.






Similar Threads
Thread Thread Starter Forum Replies Last Post
What are joins? Bhalchandra SQL Server 2000 2 July 2nd, 2007 11:29 PM
Joins nalla Oracle 0 December 14th, 2005 05:54 AM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM
Joins r_ganesh76 SQL Server 2000 2 February 10th, 2005 12:21 AM
Joins marthaj SQL Server 2000 7 June 26th, 2003 09:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.