SQL ROW

What is the SQL ROW constructor?

ROW builds an in-line composite (row) value that can be compared, inserted, or returned by a query.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ROW:

SQL ROW Full Explanation

ROW is the SQL row-value constructor defined in the SQL standard and fully implemented in PostgreSQL. It groups one or more scalar expressions into a single composite value that behaves like a record. A ROW constructor can appear anywhere an expression is allowed, including SELECT lists, INSERT ... VALUES lists, WHERE comparisons, function arguments, and RETURN statements in stored procedures. When used inside VALUES, the ROW keyword is optional in PostgreSQL but clarifies intent. The number and order of items in the constructor must match the target record structure when assigning or inserting. A ROW value supports =, <>, IS DISTINCT FROM, and other tuple comparison operators in PostgreSQL. Other dialects offer limited or no support for the ROW keyword, although parenthesized row constructors (expr1, expr2) may be accepted. Always check dialect documentation before relying on ROW.

SQL ROW Syntax

-- standalone constructor
ROW (expr1 [, expr2 ...]);

-- in INSERT
INSERT INTO table_name (col1, col2, col3)
VALUES ROW (val1, val2, val3);

-- in comparisons
WHERE (col1, col2) = ROW (val_a, val_b);

SQL ROW Parameters

Example Queries Using SQL ROW

-- Build a row literal
SELECT ROW (1, 'alice', CURRENT_DATE);

-- Compare two-column tuple to a row value
SELECT *
FROM users
WHERE (first_name, last_name) = ROW ('Alice', 'Smith');

-- Insert a row using ROW constructor
INSERT INTO products (id, name, price)
VALUES ROW (101, 'Galaxy Hoodie', 39.99);

-- Pass a row to a function expecting a composite type
SELECT process_account(ROW (u.id, u.email))
FROM users AS u
LIMIT 10;

Expected Output Using SQL ROW

Each query returns or manipulates a single composite row value
In SELECT examples, the result set contains one column whose data type is record
In INSERT, one new record is added to the table
In the WHERE clause example, only rows matching both first and last names are returned

Use Cases with SQL ROW

Construct ad-hoc composite values without defining a table type
Insert explicit rows with clearer syntax than plain VALUES parentheses
Compare multiple columns at once in WHERE clauses for readability
Pass grouped parameters to functions that take composite types
Return complex objects from stored procedures

Common Mistakes with SQL ROW

Forgetting that the column count and order must match the target table or composite type
Using ROW in dialects that do not support it (e.g., MySQL before 8.0, SQL Server)
Mixing ROW with VALUES incorrectly (doubling parentheses)
Expecting ROW to generate automatic row numbers (confusing with ROW_NUMBER)
Assuming ROW returns multiple columns instead of a single composite column in SELECT

First Introduced In

SQL-92 (row value constructor); fully supported in PostgreSQL 8.0

Frequently Asked Questions

What is the difference between ROW and a parenthesized list?

A parenthesized list like (1, 2) is treated as a row value in many dialects, but adding the ROW keyword makes the intent explicit and avoids ambiguity when mixing with arrays.

Can I omit ROW in PostgreSQL INSERT statements?

Yes. In PostgreSQL, `INSERT INTO t VALUES (1, 'a');` and `INSERT INTO t VALUES ROW (1, 'a');` are equivalent. The keyword is optional.

Does ROW create multiple columns in SELECT output?

No. A ROW constructor returns one column whose data type is record or a named composite type. Use `SELECT (ROW (1,2)).*;` to expand it into separate columns.

How do I compare multiple columns with ROW?

Tuple comparison works: `WHERE (col1, col2) = ROW (val1, val2);` evaluates to true only when both column values match the corresponding row values.