The error Operand should contain 1 column(s) is most likely caused by a subquery that’s returning more than one column.
Here’s a typical SELECT query that causes this error:
SELECT column_one,
(SELECT column_two, column_three FROM table_two)
FROM table_one;
The above subquery returns column_two and column_three, so MySQL throws the Operand should contain 1 column(s) error.
Most often, you only need to check your subquery and make sure that it returns only one column.
If you need more guidance on how to fix this MySQL error, then you may read the next section.
How to fix Operand should contain 1 column(s) error
To illustrate an example, imagine you have two tables that have related data named members and pets.
The members table contain the first_name of people who have pets as shown below:
+----+------------+----------------+
| id | first_name | country |
+----+------------+----------------+
| 1 | Jessie | United States |
| 2 | Ann | Canada |
| 3 | Joe | Japan |
| 4 | Mark | United Kingdom |
| 5 | Peter | Canada |
+----+------------+----------------+
While the pets table contain the owner and the species column as follows:
+----+--------+---------+------+
| id | owner | species | age |
+----+--------+---------+------+
| 1 | Jessie | bird | 2 |
| 2 | Ann | duck | 3 |
| 3 | Joe | horse | 4 |
| 4 | Mark | dog | 4 |
| 5 | Peter | dog | 5 |
+----+--------+---------+------+
The first_name and the owner columns are related, so you may use a subquery to display data from both tables like this:
SELECT `first_name` AS `owner_name`,
(SELECT `species`, `age`
FROM pets WHERE pets.owner = members.first_name)
FROM members;
However, the above SQL query is wrong, and it will throw an error like this:
ERROR 1241 (21000): Operand should contain 1 column(s)
This is because MySQL expects the subquery to return only one column, but the above subquery returns two.
To fix the error, you may create two subqueries with each subquery returning only one column as in the following SELECT statement:
SELECT `first_name` AS `owner_name`,
(SELECT `species`
FROM pets WHERE pets.owner = members.first_name) AS `species`,
(SELECT `age`
FROM pets WHERE pets.owner = members.first_name) AS `age`
FROM members;
While the above query works, it will throw another error once the subquery returns more than one row.
Let’s add another pet that’s owned by “Jessie” to the pets table as shown below:
+----+--------+---------+------+
| id | owner | species | age |
+----+--------+---------+------+
| 1 | Jessie | bird | 2 |
| 2 | Ann | duck | 3 |
| 3 | Joe | horse | 4 |
| 4 | Mark | dog | 4 |
| 5 | Peter | dog | 5 |
| 6 | Jessie | cat | 4 |
+----+--------+---------+------+
Now the subqueries will return two species and age rows for “Jessie”, causing another related error:
mysql> SELECT `first_name` AS `owner_name`,
-> (SELECT `species`
-> FROM pets WHERE pets.owner = members.first_name)
-> FROM members;
ERROR 1242 (21000): Subquery returns more than 1 row
To properly fix the error, you need to replace the subquery with a JOIN clause:
SELECT `first_name` AS `owner_name`, `species`, `age`
FROM members JOIN pets
ON members.first_name = pets.owner;
Subqueries can be used to replace JOIN clauses only when you need to SELECT data from one table, but you need to filter the result by another table column.
For example, maybe you have some owner names in the pets table that aren’t recorded in the members table. You can use a subquery in the WHERE clause to display rows in the pets table that are also recorded in the members table.
Here’s an example of using a subquery in the WHERE clause:
SELECT `owner`, `species`, `age`
FROM pets
WHERE `owner` IN (SELECT `first_name` FROM members);
Without using a subquery, you need to JOIN the table as shown below:
SELECT `owner`, `species`, `age`
FROM pets JOIN members
ON pets.owner = members.first_name;
The two queries above will produce the same result set.
And that’s how you can fix the Operand should contain 1 column(s) error in MySQL.
You need to check your subquery before anything else when you encounter this error.
