SQLite Except

Summary: in this tutorial, you will learn how to use the SQLite EXCEPT operator.

Introduction to SQLite EXCEPT operator

SQLite EXCEPT operator compares the result sets of two queries and returns distinct rows from the left query that are not output by the right query.

The following shows the syntax of the EXCEPT operator:

SELECT select_list1
FROM table1
EXCEPT
SELECT select_list2
FROM table2

This query must conform to the following rules:

• First, the number of columns in the select lists of both queries must be the same.
• Second, the order of the columns and their types must be comparable.

The following statements create two tables t1 and t2 and insert some data into both tables:

CREATE TABLE t1(
    v1 INT
);

INSERT INTO t1(v1)
VALUES(1),(2),(3);

CREATE TABLE t2(
    v2 INT
);
INSERT INTO t2(v2)
VALUES(2),(3),(4);

The following statement illustrates how to use the EXCEPT operator to compare result sets of two queries:

SELECT v1
FROM t1
EXCEPT 
SELECT v2
FROM t2;

The output is 1.

The following picture illustrates the EXCEPT operation:

SQLite EXCEPT examples

We will use the artists and albums tables from the sample database for the demonstration.

The following statement finds artist ids of artists who do not have any album in the albums table:

SELECT ArtistId
FROM artists
EXCEPT
SELECT ArtistId
FROM albums;

The output is as follows:

In this tutorial, you have learned how to use the SQLite EXCEPT operator to compare two queries and return unique rows from the left query that are not output by the right query.