459,576 Members | 1,399 Online
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