Match Multiple Columns at Once With Row Value Constructors

Use `(col_a, col_b) IN ((v1, v2), (v3, v4))` to filter on combinations of columns simultaneously — cleaner and less error-prone than chaining `AND`/`OR` conditions.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Return only employees who match specific (department, level) combinations

The filter targets two combinations: Engineering at level 3, and Sales at level 2. Alice matches Engineering/3. Carol matches Sales/2. Bob is Engineering/2 — that combination is not in the list. Dave is Sales/1 — also not in the list. Eve is HR/3 — not in the list. Using (department, level) IN (('Engineering', 3), ('Sales', 2)) is equivalent to the SQL Server expansion (department = 'Engineering' AND level = 3) OR (department = 'Sales' AND level = 2) and both return the same two rows. The key distinction from two separate IN lists is that department IN ('Engineering', 'Sales') AND level IN (3, 2) would produce four combinations including Engineering/2 and Sales/3, which are not the intended targets.

MySQL MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE employees (
  id INT,
  name VARCHAR(20),
  department VARCHAR(20),
  level INT
);

INSERT INTO
  employees
VALUES
  (1, 'Alice', 'Engineering', 3),
  (2, 'Bob', 'Engineering', 2),
  (3, 'Carol', 'Sales', 2),
  (4, 'Dave', 'Sales', 1),
  (5, 'Eve', 'HR', 3);

Validated query

SQL

SELECT
  id,
  name,
  department,
  level
FROM
  employees
WHERE
  (department, level) IN (('Engineering', 3), ('Sales', 2))
ORDER BY
  id;

Expected result

id	name	department	level
1	Alice	Engineering	3
3	Carol	Sales	2

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE employees (
  id INT,
  name VARCHAR(20),
  department VARCHAR(20),
  level INT
);

INSERT INTO
  employees
VALUES
  (1, 'Alice', 'Engineering', 3),
  (2, 'Bob', 'Engineering', 2),
  (3, 'Carol', 'Sales', 2),
  (4, 'Dave', 'Sales', 1),
  (5, 'Eve', 'HR', 3);

Validated query

SQL

SELECT
  id,
  name,
  department,
  level
FROM
  employees
WHERE
  (
    department = 'Engineering'
    AND level = 3
  )
  OR (
    department = 'Sales'
    AND level = 2
  )
ORDER BY
  id;

Expected result

id	name	department	level
1	Alice	Engineering	3
3	Carol	Sales	2

MySQL, MariaDB, PostgreSQL, and SQLite use (department, level) IN (('Engineering', 3), ('Sales', 2)). SQL Server expands the pairs into explicit AND/OR conditions. All engines return the same two rows.

Useful when

Where this command helps.

filtering orders by a list of (customer_id, product_id) pairs without cross-joining the two IN lists
checking whether a (year, month) combination falls within a specific set of reporting periods

Explanation

What the command is doing.

A row value constructor, written as (col_a, col_b), lets you compare tuples rather than individual values. WHERE (department, level) IN (('Engineering', 3), ('Sales', 2)) matches rows where both columns satisfy one of the listed pairs simultaneously. This is more concise and easier to read than WHERE (department = 'Engineering' AND level = 3) OR (department = 'Sales' AND level = 2), and it avoids the cross-product mistake of writing WHERE department IN ('Engineering', 'Sales') AND level IN (3, 2), which would incorrectly match Engineering/level-2 and Sales/level-3. Row value constructors also work with =, <>, <, >, <=, and >= for single-pair comparisons. PostgreSQL, MySQL 8.0+, MariaDB, and SQLite support row constructors in IN expressions. SQL Server does not support row value constructors; use an explicit AND/OR chain, or join against a VALUES derived table for larger sets.

Match Multiple Columns at Once With Row Value Constructors

Return only employees who match specific (department, level) combinations

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.