In SQLite, the UNION operator creates a compound SELECT statement that returns the results of the left and right SELECT statements. In other words, it combines the results from two queries into a single result set.

Example

Suppose we have the following tables:

SELECT * FROM Teachers;
SELECT * FROM Students;

Result:

TeacherId  TeacherName
---------  -----------
1          Warren     
2          Ben        
3          Cathy      
4          Cathy      
5          Bill       
6          Bill       

StudentId  StudentName
---------  -----------
1          Faye       
2          Jet        
3          Spike      
4          Ein        
5          Warren     
6          Bill       

We can use the UNION operator to return all teachers and students:

SELECT TeacherName FROM Teachers
UNION
SELECT StudentName FROM Students;

Result:

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

By default, the UNION operator implicitly applies a DISTINCT operation. In other words, it returns only distinct values by default. So the above results contain just one each of Warren, Cathy and Bill. This is despite the fact that the combined tables actually contain two Warrens, two Cathys, and three Bills (there are two teachers called Cathy, a teacher and a customer called Warren, and two called Bill, as well as one student called Bill).

Include Duplicates

We can use the ALL keyword to include duplicate values in the results:

SELECT TeacherName FROM Teachers
UNION ALL
SELECT StudentName FROM Students;

Result:

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

This time we got twelve rows instead of the eight that we got in our first example.

We can see that both Cathys were returned and all three Bills were returned.