SQL: Select from your select - filtering on calculated columns

Consider this SQL that lists products from the Northwind database;

SELECT 
    ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT 
FROM 
    Products

To the right you can see the resulting table. The last column shows a calculated value based on the UnitPrice column multiplied with 1,25.

Now, lets say you would like to filter the result including only products that has a UnitPriceWithVAT that is higher than 100.

You can't do like this;

SELECT 
    ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT 
FROM 
    Products
WHERE
    UnitPriceWithVAT > 100 --This wont work

This will only give you the error message "Invalid column name 'UnitPriceWithVAT'.", i.e. the calculated column is not known in the WHERE clause.

There are a few different ways to solve this problem. The most obvious one is to repeat the calculation in the WHERE clause;

--Solution A
WHERE
UnitPrice*1.25 > 100

But it's always good to avoid repeated code, so to accomplish that we could make use of a temporary table. In MS-SQL this is done with the INTO statement followed by a table name that starts with #. Then we can select from the temporary table and clean up;

--Solution B
SELECT ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT INTO #tempTable FROM Products SELECT * FROM #tempTable WHERE UnitPriceWithVAT > 100 DROP TABLE #tempTable

However, there is an even more efficient and, in my opinion, more elegant way to do this;

--Solution C
SELECT * FROM
    (
        SELECT 
            ProductName, UnitPrice, 
            UnitPrice*1.25 AS UnitPriceWithVAT 
        FROM 
            Products
    ) AS t
WHERE
    UnitPriceWithVAT > 100

In this last version the inner SELECT statement is "turned into a table" that the outer SELECT selects from, making it possible to filter on the calculated column. The important part here is to give the inner SELECT a table alias (AS t), otherwise it will not work.

In this simple example it might seem easier to just repeat the calculation in the where clause (Solution A), but what if you have a SQL-statement with many calculated columns that also should be included in the filtering, and what if you change the calculation in the SELECT clause but forgets to do it in the WHERE clause?

All three solutions will give the same result. Which one do you prefer?

5 comments :

  1. justo lo que buscaba. Gracias

    ReplyDelete
  2. Select statement is always use when anyone can fetch data from sql.Thanks for sharing worth information.

    ReplyDelete
  3. Thanks! Great Explanation and examples in understandable terms!!!

    ReplyDelete
  4. Thank you very much this is what I was looking for

    ReplyDelete