SQLite EXCEPT operator

SQLite EXCEPT operator compares the result sets of two queries and returns only distinct rows that:

  1. are in the left query
  2. are not in the right query

The SQLite EXCEPT operator can only be applied if:

  1. left and right query must have the same amount of rows
  2. left and right query must have same order of column and column data types

The syntax looks as follows:

SELECT list1
FROM table1
EXCEPT
SELECT list2
FROM table2;

Here is a simple SQLite EXCEPT operator query example:

CREATE TABLE table1(
    col1 INT
);

INSERT INTO table1(col1)
VALUES(1),(2),(3);

CREATE TABLE table2(
    col2 INT
);

INSERT INTO table2(col2)
VALUES(2),(3),(4);

SELECT col1
FROM table1
EXCEPT 
SELECT col2
FROM table2;

This will return only those entries in the left query (SELECT col1 FROM table1) that are not contained by the output of the right query (SELECT col2 FROM table2). The resulting output is thus: 1.