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