I've come across following T-SQL today:

select c from (select 1 union all select 1) as  d(c)

that yields following result:

The part that got me confused was d(c)

While trying to understand what's going on I've modified T-SQL into:

select c, b from (select 1, 2 union all select 3, 4) m(c, b)

which yields following result:

It was clear that d & m are table reference while letters in brackets c & b are reference to columns. I wasn't able to find relevant documentation on msdn, but curious if

1. You're aware of such syntax?
2. What would be useful use case scenario?

CodePudding user response：

select c from (select 1 union all select 1) as d(c)

is the same as

select c from (select 1 as c union all select 1) as d

In the first query you did not name the column(s) in your subquery, but named them outside the subquery,

In the second query you name the column(s) inside the subquery

If you try it like this (without naming the column(s) in the subquery)

select c from (select 1 union all select 1) as d

You will get following error

No column name was specified for column 1 of 'd'

This is also in the Documentation

As for the usage, some like to write it the first method, some in the second, whatever you prefer. It's all the same

CodePudding user response：

An observation: Using the table constructor values gives you no way of naming the columns, which makes it neccessary to use column naming after the table alias:

select * from
(values
     (1,2) -- can't give a column name here
    ,(3,4)
) as tableName(column1,column2) -- gotta do it here

CodePudding user response：

You've already had comments that point you to the documentation of how derived tables work, but not to answer you question regarding useful use cases for this functionality.

Personally I find this functionality to be useful whenever I want to create a set of addressable values that will be used extensively in your statement, or when I want to duplicate rows for whatever reason.

An example of addressable values would be a much more compelx version of the following, in which the calculated values in the v derived table can be used many times over via more sensible names, rather than repeated calculations that will be hard to follow:

select p.ProductName
      ,p.PackPricePlusVAT - v.PackCost as GrossRevenue
      ,etc
from dbo.Products as p
    cross apply(values(p.UnitsPerPack * p.UnitCost
                      ,p.UnitPrice * p.UnitsPerPack * 1.2
                      ,etc
                      )
               ) as v(PackCost
                     ,PackPricePlusVAT
                     ,etc
                     )

and an example of being able to duplicate rows could be in creating an exception report for use in validating data, which will output one row for every DataError condition that the dbo.Product row satisfies:

select p.ProductName
      ,e.DataError
from dbo.Products as p
    cross apply(values('Missing Units Per Pack'
                      ,case when p.SoldInPacks = 1 and isnull(p.UnitsPerPack,0) < 1 then 1 end
                      )
                     ,('Unusual Price'
                      ,case when p.Price > (p.UnitsPerPack * p.UnitCost) * 2 then 1 end
                      )
                     ,(etc)
               ) as e(DataError
                     ,ErrorFlag
                     )
where e.ErrorFlag = 1

If you can understand what these two scripts are doing, you should find numerous examples of where being able to generate additional values or additional rows of data would be very helpful.