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?

6 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
  5. On the other hand, gamers thinking about a huge slots deposit match can find a lot as} 1000's of dollars in bonus money ready for them at Red Dog. Players in search of a one-stop-shop casino that accounts for in-depth virtual sports betting will find Ignition’s library appropriate for months to 카지노사이트 come. Licensed gambling sites are held to the identical guidelines and regulatory requirements as your favourite in-person casino. These regulations guarantee on-line gambling’s safety and reliability through placing measures in place that protect your private data. At any fee, do verify your local laws {to guarantee that|to make sure that} your chosen betting web site or on-line sportsbook is legal to use the place may be}.

    ReplyDelete