Subqueries By columns returned (a, b) IN · pairwise · non-pairwise

Multiple-Column Subquery

When the inner query returns several columns, the outer query compares whole rows at a time. Pairwise comparisons keep columns linked; non-pairwise compares them independently — and can mislead.

What a multiple-column subquery is

A multiple-column subquery returns rows with more than one column. The outer query then compares against an entire row at a time, using a row constructor on the left of the operator: (col1, col2) IN (...).

This is the right tool when "matching" requires several columns to line up at once — e.g. find employees whose (department, job) combination matches a specific pair, or compare two whole rows for equality.

Two flavours

Type	How it compares
Pairwise	Treats each row as a unit — both columns must match together.
Non-pairwise	Compares each column independently with its own subquery.

Pairwise is what people usually want: it preserves the relationship between columns. Non-pairwise can produce surprising rows when the two columns aren't actually correlated.

Demo dataset — employees

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    email          VARCHAR(100),
    phone_number   VARCHAR(20),
    hire_date      DATE,
    job_id         INT,
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(100, 'Steven',    'King',      'steven.king@x.org',     '515.123.4567', '1987-06-17',  4, 24000.00, NULL,  9),
(101, 'Neena',     'Kochhar',   'neena.kochhar@x.org',   '515.123.4568', '1989-09-21',  5, 17000.00,  100,  9),
(115, 'Alexander', 'Khoo',      'alexander.khoo@x.org',  '515.127.4562', '1995-05-18', 13,  3100.00,  114,  3),
(116, 'Shelli',    'Baida',     'shelli.baida@x.org',    '515.127.4563', '1997-12-24', 13,  2900.00,  114,  3),
(200, 'Jennifer',  'Whalen',    'jennifer.whalen@x.org', '515.123.4444', '1987-09-17',  3,  4400.00,  101,  1),
(201, 'Michael',   'Hartstein', 'michael.h@x.org',       '515.123.5555', '1996-02-17', 10, 13000.00,  100,  2),
(202, 'Pat',       'Fay',       'pat.fay@x.org',         '603.123.6666', '1997-08-17', 11,  6000.00,  201,  2),
(203, 'Susan',     'Mavris',    'susan.mavris@x.org',    '515.123.7777', '1994-06-07',  8,  6500.00,  101,  4);

Example 1 — pairwise comparison

Find employees in the same (department, manager) combination as Alexander Khoo.

SQL

SELECT employee_id, first_name, department_id, manager_id
FROM   employees
WHERE  (department_id, manager_id) IN (
    SELECT department_id, manager_id
    FROM   employees
    WHERE  first_name = 'Alexander'
      AND  last_name  = 'Khoo'
);

Both department_id and manager_id must match the values from Alexander's row. Anyone who shares either one alone won't qualify.

Result

employee_id	first_name	department_id	manager_id
115	Alexander	3	114
116	Shelli	3	114

Example 2 — non-pairwise comparison

Same idea, but written with two separate IN conditions. Each column is checked independently.

SQL

SELECT employee_id, first_name, department_id, manager_id
FROM   employees
WHERE  department_id IN (SELECT department_id FROM employees WHERE first_name = 'Alexander')
  AND  manager_id    IN (SELECT manager_id    FROM employees WHERE first_name = 'Alexander');

⚠ Why non-pairwise can mislead. If "Alexander" appears in two separate (dept, manager) combos, this query matches any row that has either valid department alongside either valid manager — even if no original row had that mix. Pairwise comparison is safer when the columns are related.

Pairwise vs non-pairwise visually

Example 3 — find rows that match the (job, salary) of any boss

SQL

-- People whose (job_id, salary) match any manager's
SELECT first_name, job_id, salary
FROM   employees
WHERE  (job_id, salary) IN (
    SELECT job_id, salary
    FROM   employees
    WHERE  employee_id IN (
        SELECT DISTINCT manager_id
        FROM   employees
        WHERE  manager_id IS NOT NULL
    )
);

Where it works

Multiple-column subqueries fit in:

WHERE — (col1, col2) IN (subquery) or NOT IN.
HAVING — same pattern with grouped columns.
FROM — using the subquery as an inline view.

They cannot appear in the SELECT list (which expects exactly one value per output row).

Recap

Multiple-column subqueries return rows with several columns; compare with row constructors: (a, b) IN (...).
Pairwise keeps columns linked — usually what you want.
Non-pairwise checks each column independently — convenient but can match unrelated rows.
Live in WHERE / HAVING / FROM, never in SELECT.