If you’re getting an error that reads “The used SELECT statements have a different number of columns” in MySQL, it’s probably because you’re using the EXCEPT, INTERSECT, or UNION clause, but with a different number of columns for each query.

When we use these clauses, both queries must select the same number of columns. For example, if the first query has two columns in its SELECT list, then the second query must also have two columns in its SELECT list.

To fix, make sure both queries select the same number of columns.

Example of Error

Here’s an example of code that produces the error:

SELECT c1 FROM t1
UNION
SELECT c1, c2 FROM t2;

Result:

ERROR 1222 (21000): The used SELECT statements have a different number of columns

Here, the first query selects one column but the second query selects two. This isn’t allowed when using the UNION clause. Both queries must select the same number of columns.

In this case, I was using the UNION clause to join each SQL statement, but we’d get the same error when using INTERSECT or EXCEPT.

Here it is with INTERSECT:

SELECT c1 FROM t1
INTERSECT
SELECT c1, c2 FROM t2;

Result:

ERROR 1222 (21000): The used SELECT statements have a different number of columns

And here it is with EXCEPT:

SELECT c1 FROM t1
EXCEPT
SELECT c1, c2 FROM t2;

Result:

ERROR 1222 (21000): The used SELECT statements have a different number of columns

Solution

Regardless of which clause we’re using when we get the error, the solution is the same – ensure that both queries have the same number of columns:

SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2;

Result:

+-------+------+
| c1    | c2   |
+-------+------+
| Black | Dog  |
| White | Dog  |
| Black | Cat  |
+-------+------+

In this case I added a column to the first query and this resolved the issue.