|
|
Posted by Erland Sommarskog on 11/04/05 00:41
(JayCallas@hotmail.com) writes:
> Is it better to so one big SELECT / JOIN / WHERE statement? As in
>
> SELECT * FROM T1
> JOIN T2 ON T2.[Col1] = T1.[Col1]
> JOIN T3 ON T3.[Col2] = T1.[Col2] AND T3.[Col3] = T1.[Col3]
> and so on...
> WHERE T1.[Account] IN ('123', '456', '789')
>
> OR is it better to do an inner SELECT / WHERE and pass that to a SELECT
> / JOIN? As in
>
> SELECT * FROM
> (
> SELECT * FROM T1
> WHERE T1.[Account] IN ('123', '456', '789')
> ) IT
> JOIN T2 ON T2.[Col1] = IT.[Col1]
> JOIN T3 ON T3.[Col2] = IT.[Col2] AND T3.[Col3] = IT.[Col3]
> and so on...
That's mainly an esthetic question. The optimizer will recast the
operators if it thinks that gives a better plan, as long this does
not alter the result.
I would probably to it the first way, mainly of old habits.
> How does this apply to situations where there is a UNION involved? Do I
> do the union and then apply WHERE and JOIN to filter out rows and get
> additional data, respectively, or do I filter out rows inside the union
> and take the combined set and do the JOINS?
I guess the optimizer is smart enough to handle this as well. In this
case I prefer:
> SELECT * FROM
> (
> SELECT T1.[Col1], T1.[Col2] FROM T1
> UNION ALL
> SELECT T2.[Col1], T2.[Col2] FROM T2
> ) CT
> JOIN T2 ON T2.[Col1] = CT.[Col1]
> JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]
> and so on...
> WHERE CT.[Account] IN ('123', '456', '789')
Mainly because I don't have to repeat the conditions. But it can be
worth looking at the query plan, to see whether the opimizer cares.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|