SQL Server ‘AS’ alias unexpected syntax

I’ve come across following T-SQL today:

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

that yields following result:

c
-----------
1
1

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:

c           b
----------- -----------
1           2
3           4

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?

Answer

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.