📋

Description

MariaDB Error 1222 indicates that you are attempting to combine multiple SELECT statements (typically with operators like UNION, UNION ALL, INTERSECT, or EXCEPT) where the number of columns returned by each SELECT statement is not identical. For these set operations to succeed, every participating SELECT query must project the same number of columns, in the same order, and with compatible data types.

💬

Error Message

The used SELECT statements have a different number of columns

🔍

Known Causes

4 known causes

⚠️

Mismatched Column Counts in UNION

The most common cause, occurring when two or more SELECT statements combined with UNION or UNION ALL operators do not return an identical number of columns.

⚠️

Inconsistent Columns in Set Operations

Similar to UNION, set operators like INTERSECT and EXCEPT also mandate that all involved SELECT statements yield the exact same number of columns.

⚠️

Typographical Errors in SELECT List

Minor mistakes such as forgetting to list a column, adding an extra comma, or miscounting columns can lead to an unequal number of expressions in the SELECT clauses.

⚠️

Dynamic Query Generation Issues

When SQL queries are built programmatically, logic errors in the application code can inadvertently generate SELECT statements with varying column counts for combined operations.

🛠️

Solutions

3 solutions available

1. Align Column Counts in SELECT Statements easy

Ensure all SELECT statements within the UNION have the exact same number of columns.

1

Carefully examine each `SELECT` statement that is part of the `UNION` or `UNION ALL` operation.

2

Count the number of columns in each `SELECT` statement. They must be identical.

3

If the column counts differ, adjust the `SELECT` statements. You can achieve this by:

4

Adding placeholder values (like `NULL` or a specific constant) to `SELECT` statements that have fewer columns.

SELECT column1, column2 FROM table1
UNION ALL
SELECT columnA, NULL AS column2 FROM table2; -- Added NULL to match column count

5

Removing columns from `SELECT` statements that have more columns, if they are not intended to be part of the unioned result.

SELECT column1, column2 FROM table1
UNION ALL
SELECT columnA, columnB, columnC FROM table2; -- Assuming columnC is extraneous

6

Ensure that the data types of corresponding columns are compatible or can be implicitly converted by MariaDB.

2. Use Aliases for Consistent Column Naming easy

Employ column aliases to standardize the output names and implicitly align column structures.

1

For each `SELECT` statement in your `UNION` query, assign an alias to each selected column using the `AS` keyword.

2

Ensure that the aliases used in each `SELECT` statement are consistent, even if the underlying column names or expressions are different.

SELECT id AS record_id, name FROM users
UNION ALL
SELECT product_id AS record_id, product_name FROM products;

-- The 'record_id' alias helps ensure consistency even though the source columns are 'id' and 'product_id'.

3

This approach helps MariaDB interpret the structure of the combined result set more reliably, especially when dealing with different table structures or derived columns.

3. Inspect and Reconstruct the Query medium

Methodically break down and rebuild the UNION query to identify the exact point of inconsistency.

1

Isolate each `SELECT` statement from the `UNION` query. Run them individually to verify their output.

SELECT column1, column2 FROM table1;

2

Start with the first two `SELECT` statements and combine them with `UNION ALL`.

SELECT column1, column2 FROM table1
UNION ALL
SELECT columnA, columnB FROM table2;

3

If this combination works, add the third `SELECT` statement and test again.

SELECT column1, column2 FROM table1
UNION ALL
SELECT columnA, columnB FROM table2
UNION ALL
SELECT columnX, columnY FROM table3;

4

Continue this incremental approach. The error will occur when you add the `SELECT` statement that has a different column count or incompatible column structure.

5

Once the problematic `SELECT` statement is identified, apply the column alignment or aliasing techniques described in the previous solutions.

🔗

Related Errors

5 related errors

MariaDB Error 1222: Inconsistent UNION Column Counts