SQLite Basics: Check for Related Rows with EXISTS

October 20, 2026

Use EXISTS when a report needs to know whether a related row is present. These examples select customers with orders, find missing matches, and add a yes-or-no flag without multiplying the outer rows.

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. Find customers with paid orders

Save as paid-customers.sql:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Bo'), (3, 'Cy');
INSERT INTO orders VALUES (10, 1, 'paid'), (11, 1, 'paid'), (12, 2, 'pending');
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.id AND o.status = 'paid'
)
ORDER BY c.id;

Run:

python3 run-sql.py paid-customers.sql

Output:

1|Ada

The subquery refers to the current customer through c.id. Ada appears once even though two orders match. SELECT 1 is a convention: EXISTS checks whether a row is returned, not the selected value.

2. Find items without a matching scan

Save as missing-scans.sql:

CREATE TABLE parcels (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE scans (parcel_id INTEGER, event TEXT);
INSERT INTO parcels VALUES (1, 'Box A'), (2, 'Box B'), (3, 'Box C');
INSERT INTO scans VALUES (1, 'received'), (1, 'sorted'), (NULL, 'unassigned');
SELECT p.id, p.label
FROM parcels AS p
WHERE NOT EXISTS (
    SELECT 1 FROM scans AS s WHERE s.parcel_id = p.id
)
ORDER BY p.id;

Run:

python3 run-sql.py missing-scans.sql

Output:

2|Box B
3|Box C

NOT EXISTS keeps parcels with no matching row. The unassigned scan matches no parcel. A nullable subquery column makes NOT IN behave differently, so do not swap the two forms without considering NULL.

3. Add an existence flag to every row

Save as review-flags.sql:

CREATE TABLE documents (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE reviews (document_id INTEGER, note TEXT);
INSERT INTO documents VALUES (1, 'Setup'), (2, 'Deploy'), (3, 'Backup');
INSERT INTO reviews VALUES (1, NULL), (1, 'Looks good'), (3, NULL);
SELECT d.title, EXISTS (
    SELECT r.note FROM reviews AS r WHERE r.document_id = d.id
) AS has_review
FROM documents AS d
ORDER BY d.id;

Run:

python3 run-sql.py review-flags.sql

Output:

Setup|1
Deploy|0
Backup|1

The flag is 1 for a match and 0 otherwise. Backup has a review row even though its note is NULL. Filtering the subquery by r.note IS NOT NULL would answer a different question: whether a written note exists.

See SQLite’s EXISTS and subquery reference. Keep the relationship condition explicit: omitting it would ask whether any qualifying row exists anywhere in the inner table.

Clean up

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

rm -- run-sql.py paid-customers.sql missing-scans.sql review-flags.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on October 20, 2026

SQLite Basics: Choose Labels and Sort Priorities with CASE Python Basics: Working with Live Dictionary Views