Multi-Column Subquery in SQL with Examples

Most subqueries compare a single column against a single value or list. But sometimes a match depends on a combination of columns together — such as finding appointments where both the doctor_id and appointment_date exactly match another specific pairing. A multi-column subquery, using row constructor syntax, compares several columns as a single unit in one clean condition.

Key Definitions

• Multi-column subquery: A subquery that returns more than one column, compared against the outer query using a row constructor.
• Row constructor: SQL syntax using parentheses, such as (col1, col2), to treat multiple columns as a single comparable unit.

What You'll Learn

• Define a multi-column subquery and the row constructor syntax it uses.
• Write a query comparing multiple columns against a subquery result using IN.
• Understand when a multi-column comparison is necessary versus using several separate conditions.
• Recognize the readability benefit of row constructors over chained AND conditions.

Detailed Explanation

Suppose you want to find appointments that share the exact same doctor_id and appointment_date combination as any Cancelled appointment — perhaps to detect scheduling conflicts. Without row constructors, you'd need a subquery combined with several separate AND conditions, which becomes unwieldy. With a multi-column subquery, you write `WHERE (doctor_id, appointment_date) IN (SELECT doctor_id, appointment_date FROM appointments WHERE status = 'Cancelled')`. This compares the pair of columns together as a single tuple against each pair returned by the subquery, matching only when both values align simultaneously for the same row.

This technique is less common than single-column subqueries but appears in interview questions specifically to test whether a candidate understands that SQL can compare multiple columns as a unit, not just one at a time.

Visual Summary

Two parenthesized pairs side by side: outer pair (doctor_id, appointment_date) = (101, '2026-05-03') being checked against a list of pairs from the subquery: [(101, '2026-05-03'), (102, '2026-05-10')], with an arrow labeled 'Tuple match — both values align' pointing to the matching pair.

Quick Reference

Approach	Syntax Style	Readability
Multi-column subquery (row constructor)	WHERE (col1, col2) IN (SELECT col1, col2 ...)	Clean, single condition
Multiple single-column subqueries	WHERE col1 IN (...) AND col2 IN (...)	Can incorrectly match mismatched pairs

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Multi-column subquery: find appointments matching the same
-- doctor_id AND appointment_date as any Cancelled appointment
SELECT appointment_id, doctor_id, appointment_date, status
FROM appointments
WHERE (doctor_id, appointment_date) IN (
  SELECT doctor_id, appointment_date
  FROM appointments
  WHERE status = 'Cancelled'
);

The inner subquery returns the (doctor_id, appointment_date) pair for appointment 303, which is Cancelled: (101, '2026-05-03'). The outer query then finds any row — including appointment 303 itself — whose doctor_id and appointment_date pair exactly matches this tuple, which is exactly how row constructor comparisons work: both values must align together, not independently.

Real-World Examples

• Airline systems use multi-column subqueries to detect duplicate bookings matching both flight_number and passenger_id together.
• Retail systems use multi-column subqueries to find order line items matching both product_id and warehouse_id for stock reconciliation.
• Scheduling systems use multi-column subqueries to detect conflicting bookings sharing both a room_id and a time_slot.

Common Mistakes to Avoid

• Using two separate single-column subqueries joined with AND when a true combined-pair match is actually needed.
• Forgetting the parentheses required around the column list in row constructor syntax.
• Mismatching the column order between the outer tuple and the subquery's SELECT list, causing incorrect comparisons.

Interview Questions

Q1. What is a multi-column subquery?

A multi-column subquery returns more than one column, and is compared against the outer query using a row constructor — parenthesized column pairs like (col1, col2) — so multiple values are matched together as a single unit.

Q2. Why use a multi-column subquery instead of two separate single-column subqueries with AND?

Comparing two columns independently with separate subqueries and AND can incorrectly match rows where each column matches a different, unrelated row in the subquery result. A row constructor guarantees both columns are matched from the exact same underlying row together.

Practice MCQs

1. What syntax is used to compare multiple columns together in a subquery condition?

1. AND between two subqueries
2. Row constructor: (col1, col2) IN (...)
3. UNION of two subqueries
4. A CROSS JOIN

Answer: B. Row constructor: (col1, col2) IN (...)

Explanation: Row constructor syntax groups multiple columns into a single comparable tuple, matched against equivalent tuples returned by the subquery.

2. Why might two separate single-column subqueries with AND produce incorrect results compared to a row constructor?

1. They always cause a syntax error
2. Each column could match a different unrelated row from the subquery
3. They are slower but always correct
4. They cannot be used with SELECT

Answer: B. Each column could match a different unrelated row from the subquery

Explanation: Independent single-column conditions don't guarantee the matched values come from the same original row, while a row constructor enforces that both values are matched together as a pair.

Quick Revision Points

• Row constructor syntax: WHERE (col1, col2) IN (SELECT col1, col2 FROM ...).
• Column order in the outer tuple must match the column order in the subquery's SELECT list.
• Multi-column subqueries are less common but test deeper SQL comparison understanding in interviews.

Conclusion

• Multi-column subqueries compare several columns together as a single unit using row constructor syntax.
• This guarantees matched values come from the same row in the subquery result, unlike independent single-column conditions.
• Column order in the outer and inner comparisons must align exactly.

A multi-column subquery uses row constructor syntax — parenthesized column groups like (doctor_id, appointment_date) — to compare multiple columns together as a single unit against a subquery's result. This ensures that matched values genuinely come from the same underlying row in the subquery, avoiding the incorrect cross-matching that can occur when using separate single-column subqueries joined with AND. While less frequently used than single-column subqueries, this pattern appears in interview questions designed to test deeper understanding of SQL comparison semantics.