1

In a recent SQL Server 2008 R2 Project, I encountered many queries in which, there was an outer Select with Field names that exactly matched the field names from the Select Statement within it. Then - after the last parenthesis around the inner query - there was a where clause.

E.g. [pseudocode]

Select Field1, Field2, Field3
From
(
 Select Field1, Field2, Field3 
 From Table1 
 Inner Join Table2 ON . . . multiple joins . . .
) As A
Where Field 1 = SomeValue AND Field 2 <> SomeOtherValue

Is there a benefit to wrapping the inner Select statement in the outer Select statement rather than just adding the Where clauses to the end of the inner Select Statement?

I should add that some of the fields in the inner Select consist of Case Statements. E.g.

CASE 
   WHEN Year(tbl.[somedate]) = #### THEN NULL 
   ELSE tbl.[someotherdate] 
END AS Field2

Thanks!

Talay
  • 349
  • 1
  • 5
  • 17
  • 1
    Maybe there was a `UNION ALL` in an earlier version. – Tim Schmelter Sep 21 '15 at 15:06
  • 2
    Take a look at this answer demonstrating aliasing fields in a derived table. http://stackoverflow.com/questions/2654750/multiple-aggregate-functions-in-one-sql-query-from-the-same-table-using-differen/2654867#2654867 – Jeremy Sep 21 '15 at 15:07
  • If someone created the inner query and you didn't want to mess with it, this is a good way to wrap it. – ergonaut Sep 21 '15 at 15:08
  • 2
    In this case better to use CTE it is more understandable – The Reason Sep 21 '15 at 15:10
  • 1
    there are several reasons to do it - if, for instance, one of the columns in the derived table is the result of a window function and you need to filter your result set by it. However, as to your pseudocode example, it seems to be meaningless. – Zohar Peled Sep 21 '15 at 15:11
  • Thanks @ergonaut & Zohar. Not wanting to mess with the existing query written by someone else is a good reason. And as I added later to my post - there are several Case Statements in the derived table and filtering by those fields' aliases becomes easy. Thanks to all others for your help too! – Talay Sep 21 '15 at 15:23
  • One reason that I use this is to order the results in a custom way, if you consider hard coding the select order in the sub select. – Dane Sep 21 '15 at 16:02
  • A lot of code generators do things like this, too. It's not very readable or maintainable necessarily, but it's very easy to write in such a way that a program can write the SQL and not screw it up. – Bacon Bits Sep 21 '15 at 18:18
  • As Jeremy suggests, you can't reference a calculated value, e.g. `case ... as Field2`, within the `select` that performs the calculation. To use the value in the `where` clause involves either repeating the calculation or wrapping the `select` in another `select` so that it appears to be just another column. – HABO Sep 22 '15 at 03:39
  • Some reasons for using an inner-select like that is like reasons for creating a VIEW ([link](http://stackoverflow.com/q/1278521/4519059)) ;). – shA.t Sep 28 '15 at 04:46

1 Answers1

0

In this case, the WHERE clause cannot reference Feild2 (which is based on a case statement). By nesting the query it can because it's already been evaluated. The WHERE clause cannot see aliases, so you'd have to repeat the CASE statement in the WHERE clause.

I'd still prefer a CTE or other options personally.

Mike Lockett
  • 26
  • 5