Joins vs SubQueries

Hi,

Can any one please let me know which one is better in performance, Joins or sub-queries?

Any other differences between joins and sub-queries plz let me know.

Thanks

Pradeep

pradeepyr:

I would suggest that the best way to answer your question is to take quick benchmarks whenever you have doubt. In general (1) look at the execution plan, (2) query execution time and (3) query IO statistics and judge based on this information. This is all information that is readily obtainable from the Query Analyzer in SQL Server 2000 and SQL Server Management Studio in SQL Server 2005.

Dave

|||

joins and subqueries solve different purposes, and cant/may not be replaced just for the sake of it...

**use a subquery when u may not need the column from the table used the subquery in the result set of outer query.....

join is usually easy to optimize than a subquery... as join joins the whole tables based on a condition, use it when u retrive more data....

all said if u have a confusion, and choice of using both,(both givin same result), check the query plans, and decide, or simply go for joins , excluding the one condition i mentioned**..

JOINS to Sub-Queries -vs- JOINS to Tables

SQL Server 2000

Howdy All.

Is it going to be faster to join several tables together and then
select what I need from the set or is it more efficient to select only
those columns I need in each of the tables and then join them together
?

The joins are all Integer primary keys and the tables are all about the
same.

I need the fastest most efficient method to extract the data as this
query is one of the most used in the system.

Thanks,

CraigOn 11 Aug 2005 09:24:08 -0700, csomberg@.dwr.com wrote:

>SQL Server 2000
>Howdy All.
>Is it going to be faster to join several tables together and then
>select what I need from the set or is it more efficient to select only
>those columns I need in each of the tables and then join them together
>?
>The joins are all Integer primary keys and the tables are all about the
>same.
>I need the fastest most efficient method to extract the data as this
>query is one of the most used in the system.
>Thanks,
>Craig

Hi Craig,

I'm not sure I understand your question. Are you asking about the
performance difference between queries like these two?

SELECT A.something, B.otherthing
FROM TableA AS A
INNER JOIN TableB AS B
ON A.xxx = B.xxx
WHERE A.yyy = y
AND B.zzz = z

or

SELECT A.something, B.otherthing
FROM (SELECT xxx, something
FROM TableA
WHERE A.yyy = y) AS A
INNER JOIN (SELECT xxx, otherthing
FROM TableB
WHERE B.zzz = z) AS B
ON A.xxx = B.xxx

My first guess is that there will be no difference. The optimizer is
free to rearrange the query every way it wants, as long as the end
results are the same. They will probably result in the same execution
plan.

On the other hand, it is very hard to predict what the optimizer will
do. It often does a good job, but there still are situations where it
shows that it's just a program.

If you really want to be sure, then why don't you simply test both
against your system?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(csomberg@.dwr.com) writes:
> Is it going to be faster to join several tables together and then
> select what I need from the set or is it more efficient to select only
> those columns I need in each of the tables and then join them together
> ?
> The joins are all Integer primary keys and the tables are all about the
> same.
> I need the fastest most efficient method to extract the data as this
> query is one of the most used in the system.

Your query is open to several interpretations, so the answers you get
may not address your real issue.

If your idea is to first join two tables, get those columns into
a temp table, join that with the next table, then this is generally
not a good idea. Although, when it comes to performance there a few
definitive answers. For a certain query, this could actually be a
good strategy. But as a general approach, it's better to throw in
all tables into one query.

And you should not use SELECT * - only list the columns you actually
need.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I need the fastest most efficient method to extract the data as this query is one of the most used in the system. <<

Then test them. But my guess is that the optimizer will do them the
same way. Putting all the tables in the FROM clause will be much easier
to read and maintain, however.sql