Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Name for SELECT * FROM (VALUES (x,y)) AS TableLiteral(Col1, Col2)

The following is valid SQL syntax:

SELECT *
    FROM (VALUES ('p','q'),('x','y')) AS TableLiteral(Col1, Col2)

and returns the table:

  | Col1 | Col2
----------------
1 |  p   |  q
2 |  x   |  y

This syntax can further be used in CTEs etc.

Is there a name for this? I've been calling them "TableLiterals" by analogy with string literals and regex literals.

Is there a term that will be widely recognised.

like image 545
Brondahl Avatar asked Aug 02 '17 16:08

Brondahl


2 Answers

It is called: Table Value Constructor

Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING clause of the MERGE statement, and in the definition of a derived table in the FROM clause.

VALUES ( ) [ ,...n ]

More info about ANSI standard: F641, Row and table constructors and select without from

like image 196
Lukasz Szozda Avatar answered Oct 12 '22 01:10

Lukasz Szozda


They are called Table Value constructor

https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql

like image 34
Horaciux Avatar answered Oct 12 '22 02:10

Horaciux