SQLite EXCEPT Operator

In SQLite, the EXCEPT operator can be used to create a compound SELECT statement that returns the subset of rows returned by the left SELECT that are not returned by the right SELECT.

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 EXCEPT operator to return teachers that aren’t also students:

SELECT TeacherName FROM Teachers
EXCEPT
SELECT StudentName FROM Students;

Result:

TeacherName
-----------
Ben        
Cathy

So we only get values that appear in the Teachers table that don’t also appear in the Students table.

We can get different results, depending on which table is on the left and which is on the right. Here’s an example that puts the Students table on the left and Teachers on the right:

SELECT StudentName FROM Students
EXCEPT
SELECT TeacherName FROM Teachers;

Result:

StudentName
-----------
Ein        
Faye       
Jet        
Spike

This time we get students that aren’t also teachers.

SQLite’s EXCEPT operator returns distinct rows only. So in our example, only one row is returned for Cathy, even though there are two teachers with that name.

Some other RDBMSs allow us to include duplicates in the result by accepting an optional ALL keyword with their EXCEPT operator, but SQLite doesn’t (at least, not at the time of writing). Feel free to check SQLite’s documentation in case anything changes.

An Alternative

It’s possible to get the same result without using the EXCEPT operator. For example, we could rewrite our first example to this:

SELECT 
    DISTINCT TeacherName
FROM Teachers t 
WHERE NOT EXISTS (SELECT StudentName FROM Students s
WHERE t.TeacherName = s.StudentName);

Result:

TeacherName
-----------
Ben        
Cathy