By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,576 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,576 IT Pros & Developers. It's quick & easy.

Looking for an opinion

P: n/a
This is more a theoretical question so I do not have any DDL (working)
to post.

Let's say that I have a query which needs to be filtered for specific
accounts while also needing several joins to retrieve additional data.

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...

First glance logic says that the inner select is the way to go since
the joins would have less rows to work with, as opposed to join
everything and THEN pulling out what is not needed. But the query
planner sometimes seems to have a mind of its own... Does it know that
rows will be pulled so it does that first? If I follow the same
"structure" with many different queries does in us the same logic all
the time or do I need to try the same thing for each and check it?

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?

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')

versus

SELECT * FROM
(
SELECT T1.[Col1], T1.[Col2] FROM T1 WHERE T1.[Account] IN ('123',
'456', '789')
UNION ALL
SELECT T2.[Col1], T2.[Col2] FROM T2 WHERE T2.[Account] IN ('123',
'456', '789')
) CT
JOIN T2 ON T2.[Col1] = CT.[Col1]
JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]
and so on...

Nov 3 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(Ja*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 3 '05 #2

P: n/a
Thanks for your response Erland.

Nov 4 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.