SQLite Except

Summary: in this tutorial, you will learn how to use the SQLite EXCEPT operator to combine result sets of two queries and returns the distinct rows from the first query that are not present in the output of the second query.

Introduction to SQLite EXCEPT operator

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

The following shows the syntax of the EXCEPT operator:

SELECT select_list1
FROM table1
EXCEPT
SELECT select_list2
FROM table2;Code language: SQL (Structured Query Language) (sql)

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 (c1 INT);

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

CREATE TABLE t2 (c2 INT);

INSERT INTO
  t2 (c2)
VALUES
  (2),
  (3),
  (4);Code language: SQL (Structured Query Language) (sql)

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

SELECT c1 FROM t1
EXCEPT 
SELECT c2 FROM t2;Code language: SQL (Structured Query Language) (sql)

Output:

c1
--
1

The following picture illustrates the EXCEPT operation: