Fix “Operand Should Contain 1 Column(s)” in SQL

The symptom

A subquery sits after IN, after =, or in the SELECT list, and it selects two columns. MySQL says the operand should contain 1 column, SQL Server says only one expression can be specified, and DuckDB says the subquery returns 2 columns where it expected 1. It is about columns. When a subquery returns too many rows, the error is More than one row returned by a subquery.

product_id in (...) compares one value against a list of single values. The subquery below adds quantity, often to check it while writing, and the list no longer holds single values:

The fix: select only the column you compare

select product_name
from products
where product_id in (
  select product_id, quantity
  from order_items
  where quantity >= 3
)
order by product_name;

select product_name
from products
where product_id in (
  select product_id
  from order_items
  where quantity >= 3
)
order by product_name;

The where quantity >= 3 filter still applies. A column doesn't have to be selected to be used in the subquery's WHERE.

When you mean a pair of values

Sometimes both columns matter: an order line counts only when its order and its product both match. Put the same number of columns on the left, in parentheses:

select order_id, product_id, quantity
from order_items
where (order_id, product_id) in (
  select order_id, product_id
  from order_items
  where quantity >= 3
)
order by order_id, product_id;

DuckDB, PostgreSQL, MySQL, and Oracle accept a row on the left. SQL Server doesn't; write it as EXISTS with both conditions in the subquery's WHERE. EXISTS never has this error: it only checks whether a row comes back, so its select list doesn't matter.

One value per row in SELECT

A subquery in the SELECT list fills one cell, so it can't return a first name and a last name. Combine them into one value, or write two subqueries. A join is usually clearer:

select o.order_id,
       (select c.first_name, c.last_name
        from customers as c
        where c.customer_id = o.customer_id) as customer
from orders as o;

select o.order_id,
       (select c.first_name || ' ' || c.last_name
        from customers as c
        where c.customer_id = o.customer_id) as customer
from orders as o
order by o.order_id
limit 3;

select o.order_id, c.first_name, c.last_name
from orders as o
join customers as c on c.customer_id = o.customer_id
order by o.order_id
limit 3;

How other engines word it

Related errors

• Fix “More Than One Row Returned by a Subquery” in SQL

  A subquery used as a single value found several. Use IN, or decide which one row you want.

• Fix “Different Number of Columns” in a SQL UNION

  Each SELECT in a UNION has to return the same number of columns, in the same order.

• Fix “Column Count Doesn't Match Value Count” in SQL

  An INSERT supplies a different number of values than the columns it fills. Name the columns, then give one value for each.

Learn more

• SQL error messages explained
• Lesson 10.2 · IN with subqueries
• Lesson 10.1 · Scalar subqueries
• Lesson 10.3 · EXISTS and NOT EXISTS
• Reference · Subqueries and EXISTS