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

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.

Hi robprell,

Thanks for your explanation. I will try to use more joins.

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.
[email protected]

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.