SQLite Basics: Combine and Compare Lists with Set Operations

Compound queries combine result rows from separate SELECT statements. UNION combines lists, INTERSECT finds their overlap, and EXCEPT finds what is missing from the second list. Each example starts with its own tiny database.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Choose whether to preserve duplicates

Save as combined-names.sql:

CREATE TABLE morning (name TEXT);
CREATE TABLE afternoon (name TEXT);
INSERT INTO morning VALUES ('Ada'), ('Bo'), ('Bo');
INSERT INTO afternoon VALUES ('Bo'), ('Cy');
SELECT 'UNION ALL';
SELECT name FROM morning
UNION ALL
SELECT name FROM afternoon
ORDER BY name;
SELECT 'UNION';
SELECT name FROM morning
UNION
SELECT name FROM afternoon
ORDER BY name;

Run:

python3 run-sql.py combined-names.sql

Output:

UNION ALL
Ada
Bo
Bo
Bo
Cy
UNION
Ada
Bo
Cy

UNION ALL preserves every attendance row. UNION removes duplicates from the combined result. If you select multiple columns, the complete result row determines whether two entries are duplicates.

2. Find people on both lists

Save as shared-attendees.sql:

CREATE TABLE workshop_a (name TEXT);
CREATE TABLE workshop_b (name TEXT);
INSERT INTO workshop_a VALUES ('Ada'), ('Bo'), ('Bo'), ('Cy');
INSERT INTO workshop_b VALUES ('Bo'), ('Cy'), ('Dee');
SELECT name FROM workshop_a
INTERSECT
SELECT name FROM workshop_b
ORDER BY name;

Run:

python3 run-sql.py shared-attendees.sql

Output:

Bo
Cy

INTERSECT keeps only names found in both results, with duplicates removed. Bo appears once even though the first list contains two rows for Bo.

3. Find required files that are absent

Save as missing-files.sql:

CREATE TABLE required_files (name TEXT);
CREATE TABLE actual_files (name TEXT);
INSERT INTO required_files VALUES ('config.ini'), ('data.csv'), ('notes.txt'), ('notes.txt');
INSERT INTO actual_files VALUES ('config.ini'), ('extra.log');
SELECT name FROM required_files
EXCEPT
SELECT name FROM actual_files
ORDER BY name;

Run:

python3 run-sql.py missing-files.sql

Output:

data.csv
notes.txt

EXCEPT is directional: required minus actual finds missing files. Reversing the two SELECTs would find unexpected files. These are sample table contents; the query does not inspect the filesystem.

Every SELECT in a compound query must return the same number of columns. Use compatible meanings in the corresponding positions. Put ORDER BY at the end to sort the entire result. UNION, INTERSECT, and EXCEPT treat NULLs as equal when removing duplicate rows. See SQLite’s compound SELECT reference.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py combined-names.sql shared-attendees.sql missing-files.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.