📋

Description

MySQL Error 1222 indicates that multiple `SELECT` statements being used together do not return the same number of columns. This error commonly occurs in set operations like `UNION` or when a subquery's internal `SELECT` statements have inconsistent result structures.

💬

Error Message

The used SELECT statements have a different number of columns

🔍

Known Causes

3 known causes

⚠️

Inconsistent UNION/UNION ALL Queries

Combining `SELECT` statements with `UNION` or `UNION ALL` where each individual `SELECT` statement returns a different number of columns.

⚠️

Subqueries with Mismatched Columns

A subquery, particularly one used in an `INSERT ... SELECT` or `CREATE VIEW AS SELECT` context, internally uses `SELECT` statements that conflict in column count.

⚠️

Derived Table Column Inconsistency

Defining a derived table (a subquery in the `FROM` clause) where the internal `SELECT` statements, if combined, produce an inconsistent number of columns.

🛠️

Solutions

3 solutions available

1. Align Column Counts in UNION or UNION ALL easy

Ensure all SELECT statements in a UNION or UNION ALL query return the same number of columns.

1

Review each SELECT statement within your `UNION` or `UNION ALL` query.

2

Count the number of columns selected in the first SELECT statement. Then, count the columns in all subsequent SELECT statements.

3

Adjust the SELECT statements to ensure they all have an identical number of columns. You can achieve this by adding or removing columns, or by using `NULL` placeholders for missing columns.

Example: If one SELECT has 3 columns and another has 2, add a NULL placeholder to the second one.

SELECT col1, col2, col3 FROM table1
UNION ALL
SELECT colA, colB, NULL FROM table2;

2. Correct Subquery Column Mismatch medium

Verify that subqueries used in `SELECT` or `WHERE` clauses return only a single column when expected.

1

Identify any subqueries within your main SQL statement (e.g., in the `SELECT` list, `WHERE` clause, or `FROM` clause).

2

If a subquery is intended to return a single value (e.g., for comparison in a `WHERE` clause or as a scalar value in the `SELECT` list), ensure its `SELECT` statement specifies exactly one column.

Incorrect:
SELECT column1, (SELECT colA, colB FROM another_table WHERE id = main_table.id) AS subquery_result FROM main_table;

Correct:
SELECT column1, (SELECT colA FROM another_table WHERE id = main_table.id) AS subquery_result FROM main_table;

3

If a subquery is used in the `FROM` clause (as a derived table), ensure the number of columns it returns is consistent with how it's being joined or selected from.

3. Inspect JOIN Conditions for Column Alignment medium

When using subqueries in JOINs, ensure the column counts match the join or selection criteria.

1

Locate any `JOIN` clauses that involve a subquery (derived table).

2

Examine the `SELECT` list of the subquery. Ensure it returns a number of columns that can be meaningfully joined or selected against the other table in the `JOIN` clause.

Example: If you are joining on two columns, the subquery must return at least two columns that can be used in the `ON` clause or selected thereafter.

SELECT m.col1, s.sub_col1, s.sub_col2
FROM main_table m
JOIN (
  SELECT id, column_a, column_b FROM sub_table
) AS s ON m.id = s.id;

If the subquery was `SELECT id, column_a FROM sub_table`, and you tried to select `s.sub_col2`, you would get this error.

3

Adjust the subquery's `SELECT` list or the main query's selection to ensure column compatibility.

🔗

Related Errors

5 related errors

MySQL Error 1222: Mismatched SELECT Column Count