Questions tagged [row-value-expression]

A row-value-expression is a SQL standard expression for specifying a row value. Other languages call this a record or a tuple.

Definition

The SQL1992 Standard specifies a row value constructor as such:

<row value constructor> ::=
        <row value constructor element>
      | <left paren> <row value constructor list> <right paren>
      | <row subquery>

 <row value constructor list> ::=
      <row value constructor element>
          [ { <comma> <row value constructor element> }... ]

 <row value constructor element> ::=
        <value expression>
      | <null specification>
      | <default specification>

According to this definition, regular values and row values can thus be used interchangeably in SQL. This can be seen in expressions as these:

Using row values of degree = 1

SELECT * FROM table t
WHERE t.a = 1

Using row values of degree > 1

SELECT * FROM table t
WHERE (t.a, t.b) = (1, 2)

Support

Only few databases really support row value expressions as specified in the SQL standard. These include (with partial or full support):

• DB2
• HSQLDB
• MYSQL
• ORACLE
• POSTGRES