T-SQL: Referring computed/calculated columns in the where and group by clause

I previously blogged about how we can write more manageable and readable TSQL queries using CROSS APPLY operator here. This small post is a follow-up and will describe another scenario. Suppose we have a computed column that is a result of some complex calculation and we want to refer it in a where clause (or a group by clause). This is not straight forward since we can’t use the computed column directly in the where clause like this:

select 
  column1
  ,column2 
  ,computedcolumn = 
    Case 
      when expression1 = true then column1 * ( column4 - column 5 )
      when expression2 = true then column2 * ( column5 - column 6 )
      when expression3 = true then column3 * ( column6 - column 7 )
      else column4 - column 5
    end
from SomeTable
where computedColumn < 100

The reason is that the Where part of the query is evaluated before the Select part. For this to work, we need to duplicate the whole computation in the where clause like this:

Where
  (
    Case 
      when expression1 = true then column1 * ( column4 - column 5 )
      when expression2 = true then column2 * ( column5 - column 6 )
      when expression3 = true then column3 * ( column6 - column 7 )
      else column4 - column 5
    end
  ) < 100

Luckily, the APPLY operator comes to our rescue again. We just need to move our calculation to a CROSS APPLY section and we can refer to the calculated column by its alias in the where (as well as group by) clause:

select 
  column1
  ,column2, 
  ,computedcolumn
from SomeTable
cross apply
  (
    Select 
      computedColumn = 
        Case 
          when expression1 = true then column1 * ( column4 - column 5 )
          when expression2 = true then column2 * ( column5 - column 6 )
          when expression3 = true then column3 * ( column6 - column 7 )
          else column4 - column 5
        end
  ) ComputatedColumn1Query
where computedColumn < 100

Great! We can also use a CTE or derived table to accomplish the same:

;With TableWithComputedColumnCalculation as
(
  select 
    column1
    ,column2, 
    ,computedcolumn = 
      Case 
        when expression1 = true then column1 * ( column4 - column 5 )
        when expression2 = true then column2 * ( column5 - column 6 )
        when expression3 = true then column3 * ( column6 - column 7 )
        else column4 - column 5
      end
  from SomeTable
)

select * 
from TableWithComputedColumnCalculation
where computedColumn < 100

Notice that all the approaches I mentioned here (CROSS APPLY, CTE, derived table) are going to have the same query plan (and hence performance) and it is just a matter of personal preference which one to use.

About these ads

6 Responses to “T-SQL: Referring computed/calculated columns in the where and group by clause”

  1. Mastan Says:

    Syed Mehroz Alam Salaam,
    I found this blog by code project, once saw ur article “https://www.codeproject.com/KB/silverlight/MySilverlightDataApp.aspx” its very good and superb one. here also u explained about SQL really wonderful. I’m very happy to see such nice one. they way u said like “step by step” its so nice.

    –Mastan

  2. Syed Mehroz Alam Says:

    Waalaikum Assalam, Mastan. Thanks for your appreciation and comment.

  3. Muhammad Asif Says:

    Thats what I was looking..Thanks bro. Please also write one article for cross apply . I have seen it many websites but didnt find how its working, and how its different from other joins and in which situation we need it.

    Thanks

  4. Vladimir Says:

    Thanks for article. It help me a lot

  5. Omar Gamil Says:

    very useful!
    thank you

  6. guest3 Says:

    trying to use cross apply so i can use a calculated feild in group by. i get the following error on the group by line. ‘Outer join operators cannot be specified in a query containing joined tables.’


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 55 other followers

%d bloggers like this: