If you’re getting “Error: in prepare, SELECTs to the left and right of UNION do not have the same number of result columns…” in when trying to use the UNION operator in SQLite, it’s because one of the SELECT statements is returning more columns than the other.

When you use the UNION operator, both SELECT statements must return the same number of columns.

To fix this issue, make sure the SELECT statements return the same number of columns.

Example of Error

Here’s an example of SQL code that produces the error:

SELECT TeacherName FROM Teachers
UNION
SELECT StudentId, StudentName FROM Students;

Result:

Error: in prepare, SELECTs to the left and right of UNION do not have the same number of result columns (1)

Here, the first SELECT statement returns one column (TeacherName), but the second SELECT statement returns two columns (StudentId and StudentName).

Solution

The way to fix this issue is to ensure both SELECT statements return the same number of columns

So using the above example, we can either remove the extra column from our second SELECT statement:

SELECT TeacherName FROM Teachers
UNION
SELECT StudentName FROM Students;

Result:

TeacherName
-----------
Ben        
Bill       
Cathy      
Ein        
Faye       
Jet        
Spike      
Warren     

Or we can add another column to the first SELECT statement:

SELECT TeacherId, TeacherName FROM Teachers
UNION
SELECT StudentId, StudentName FROM Students;

Result:

TeacherId  TeacherName
---------  -----------
1          Faye       
1          Warren     
2          Ben        
2          Jet        
3          Cathy      
3          Spike      
4          Cathy      
4          Ein        
5          Bill       
5          Warren     
6          Bill       

Bear in mind that you can get different results depending on which option you choose. This is because UNION returns distinct rows by default. When we add another column, there’s a possibility that a previously duplicate row now becomes a unique row, depending on the value in the extra column.

We can also use UNION ALL, which returns duplicate values:

SELECT TeacherId, TeacherName FROM Teachers
UNION ALL
SELECT StudentId, StudentName FROM Students;

Result:

TeacherId  TeacherName
---------  -----------
1          Warren     
2          Ben        
3          Cathy      
4          Cathy      
5          Bill       
6          Bill       
1          Faye       
2          Jet        
3          Spike      
4          Ein        
5          Warren     
6          Bill