The “Operand should contain 1 column(s)” error has been so common that it has been included in the official reference manual of MySQL. But this documentation gives no real solutions to overcome the issue, and this is exactly what we are going to show you.
How To Fix “Operand Should Contain 1 Column(s)” Error
A Common Example
Let’s say you have two tables in the same database below:
|game_id|name |release_year| |-------|----------------------- |------------| |1 |RESIDENT EVIL VILLAGE |2021 | |2 |CRIS TALES |2021 | |3 |TALES OF ARISE |2021 | |4 |DEATHLOOP |2021 | |5 |FAR CRY 6 |2021 | |6 |BACK 4 BLOOD |2021 | |7 |HALO INFINITE |2021 | |8 |DYING LIGHT 2 |2021 | |9 |HORIZON FORBIDDEN WEST |2021 | |10 |ATOMIC HEART |2021 |
|game_id|publisher | |-------|----------------------- |1 |Capcom | |2 |Modus Games | |3 |Bandai | |4 |Bethesda Softworks | |5 |Ubisoft | |6 |Warner Bros | |7 |Xbox | |8 |Techland | |9 |Sony | |10 |Mundfish |
And you want to create an output like this:
Which query should you use? Some people may naturally think about a subquery like this:
SELECT SELECT(name, release_year FROM table1), publisher FROM table2
But instead of our desired result set, they would run into an error:
Operand should contain 1 column(s)
Explanation Of The Error
Why doesn’t this work? Why queries are a common tool among MySQL programmers, some aren’t aware that they can’t select multiple columns in every context.
In the example above, the first SELECT query is expecting just one column returned by the second subquery SELECT. By picking two columns (name and release_year from table1), you are returning the wrong number of columns.
It doesn’t mean that using a subquery to return multiple columns always leads to errors in MySQL. There are many legit applications for this technique, where your queries would run just fine.
However, be aware of the common mistake that we can express in this syntax:
SELECT (SELECT column1, column2 FROM table2) FROM table1;
How To Fix It
For this particular case, you can join the two tables and perform a single SELECT on the joint table. This can be done with the JOIN clause.
SELECT name, release_year, publisher FROM table1 JOIN table2;
It will run without a glitch and give you the result you are looking for.
Remember that this method doesn’t work with every column name. For instance, if you run this statement:
SELECT game_id, name, release_year, publisher FROM table1 JOIN table2;
Because there are two columns of the same film_id name in two tables, MySQL doesn’t know which one to put into the final output. What you get will be an error like this:
Column 'game_id' in field list is ambiguous
The “Operand should contain 1 column(s)” error usually happens when you feed multiple columns into a SELECT query. There are some alternative methods in MySQL that help you achieve the same results successfully.