In order to fix “ERROR 1222 (21000): The used SELECT statements have a different number of columns” while we are using UNION clause we need to understand why we get this error. We used union between 2 select queries. So both select queries were not selecting same number of columns. That’s how we got this error. Here is how to fix this error:
SELECT EmployeeName FROM Employees UNION SELECT CompanyId, CompanyName FROM Companies;
First select query is selecting “EmployeeName” and second one is selecting CompanyId and CompanyName. Clearly the number of selected columns is not the same. So here is the correct query that will fix the error.
SELECT EmployeeName FROM Employees UNION SELECT CompanyName FROM Companies;
Or we can also select two columns with UNION clause.
SELECT EmployeeId,EmployeeName FROM Employees UNION SELECT CompanyId,CompanyName FROM Companies;
Also UNION clause can only select distinct row. This means that duplicate rows will not be selected. In order to select duplicate we need to use “UNION ALL” clause.
SELECT EmployeeId,EmployeeName FROM Employees UNION ALL SELECT CompanyId,CompanyName FROM Companies;
For any issues while fixing this error feel free to drop a comment below.