How to Fix \'Error 1222 (21000): The used SELECT statements have a different number of columns\' in MySQL UNION Operations?

Error: Discordant Column Count in SELECT Statements

In MySQL, UNION and UNION ALL operations demand that the participating SELECT statements possess an identical number of columns within their SELECT clauses. Additionally, the data types of the columns must correspond at each position.

The provided query violates this rule:

<code class="sql">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)
UNION SELECT fid2 FROM friends
WHERE (friends.fid2 = 1) AND (friends.fid1 < 1)
ORDER BY RAND()
LIMIT 6;

The first SELECT statement returns all columns from the "friends" and "users" tables, while the second SELECT statement returns only the "fid2" column from the "friends" table. This discrepancy in column count leads to the following error message:

<code class="error">ERROR 1222 (21000): The used SELECT statements have a different number of columns from the following:

To resolve the error, ensure that all SELECT statements in the UNION operation have the same number of columns. The easiest way to achieve this is by using an explicit column list in each SELECT statement:

<code class="sql">SELECT f.*, u.*
FROM FRIENDS AS f
JOIN USERS AS u ON u.uid = f.fid2
WHERE f.fid1 = 1
AND f.fid2 > 1
UNION
SELECT f.*, u.*
FROM FRIENDS AS f
JOIN USERS AS u ON u.uid = f.fid1
WHERE f.fid2 = 1
AND f.fid1 < 1
ORDER BY RAND()
LIMIT 6;</code>

By explicitly specifying the columns in each SELECT statement, MySQL can ensure that the column count and data types match, allowing the UNION operation to proceed without 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 MySQL UNION Operations?. For more information, please follow other related articles on the PHP Chinese website!