How to Fix \"ERROR 1222 (21000): The used SELECT statements have a different number of columns\" in UNION Statements?

Error: Disparity in Column Count Between UNIONed SELECT Statements

In the provided query, you encounter the error: "ERROR 1222 (21000): The used SELECT statements have a different number of columns." This error arises when the SELECT statements in a UNION statement return unequal column counts.

In your query, the first SELECT statement:

SELECT * FROM friends
LEFT JOIN users AS u1 ON users.uid = friends.fid1
LEFT JOIN users AS u2 ON users.uid = friends.fid2
WHERE (friends.fid1 = 1) AND (friends.fid2 > 1)

returns three columns: friends.fid1, friends.fid2, and columns from the joined users tables (u1 and u2). However, the second SELECT statement:

SELECT fid2 FROM friends
WHERE (friends.fid2 = 1) AND (friends.fid1 < 1)

returns only a single column: friends.fid2.

UNION Requirements for Column Matching

For a UNION operation to succeed, all the SELECT statements being combined must adhere to the following requirements:

• Matching Column Count: Each SELECT statement must return the same number of columns.
• Matching Column Data Types: The data types of the columns at each position in the SELECT clause must match.

Resolution

To resolve this error, you must ensure that all the SELECT statements in your UNION operation return the same number of columns with matching data types. For example, you can rewrite your query as:

SELECT friends.*, u.*
FROM friends
JOIN users AS u ON u.uid = friends.fid2
WHERE friends.fid1 = 1 AND friends.fid2 > 1
UNION
SELECT friends.*, u.*
FROM friends
JOIN users AS u ON u.uid = friends.fid1
WHERE friends.fid2 = 1 AND friends.fid1 < 1
ORDER BY RAND()
LIMIT 6;

In this revised query, both SELECT statements return the same number of columns (five) with matching data types, resolving the error.

The above is the detailed content of How to Fix \"ERROR 1222 (21000): The used SELECT statements have a different number of columns\" in UNION Statements?. For more information, please follow other related articles on the PHP Chinese website!