SQLite Basics: Join Related Rows

Related facts often live in separate tables: a customer has an ID, and each order records that customer’s ID. A join combines those facts for a report. The key question is whether a row without a match should remain visible.

Each example defines small inline tables with WITH and VALUES. The examples intentionally include a customer with no orders and a customer with multiple orders, so the result makes the join’s behavior visible.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. No packages are needed. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. 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 small runner as run-sql.py. It executes SQL against a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. Put each statement’s ending semicolon at the end of its own line, as below; 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. Show only matching orders

Save as matching-orders.sql:

WITH customers(id, name) AS (
  VALUES (1, 'Mina'), (2, 'Alex'), (3, 'Jo')
), orders(id, customer_id, cents) AS (
  VALUES (10, 1, 1200), (11, 1, 800), (12, 2, 900)
)
SELECT c.name, o.id, o.cents
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Run:

python3 run-sql.py matching-orders.sql

Output:

Mina|10|1200
Mina|11|800
Alex|12|900

The aliases c and o keep column references short while making their table clear. The ON condition pairs each order with its customer. Mina appears twice because she has two matching orders; a join does not automatically collapse them.

Before totaling joined data, check the relationship’s shape. Joining two separate one-to-many lists can multiply rows and inflate totals. Aggregate those lists separately when that is what the report needs.

2. Keep customers without qualifying orders

Save as optional-orders.sql:

WITH customers(id, name) AS (
  VALUES (1, 'Mina'), (2, 'Alex'), (3, 'Jo')
), orders(id, customer_id, cents) AS (
  VALUES (10, 1, 1200), (11, 1, 800), (12, 2, 900)
)
SELECT c.name, o.id, o.cents
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.cents >= 1000
ORDER BY c.id, o.id;

Run:

python3 run-sql.py optional-orders.sql

Output:

Mina|10|1200
Alex|NULL|NULL
Jo|NULL|NULL

A LEFT JOIN preserves every customer. Alex has an order, but it fails the minimum amount in the join condition. Both Alex and Jo therefore receive missing order values in this report.

Moving o.cents >= 1000 into WHERE would remove those missing-value rows after the join. Keep a condition in ON when it defines which optional matches qualify and you still need all customers.

3. Find customers with no orders

Save as no-orders.sql:

WITH customers(id, name) AS (
  VALUES (1, 'Mina'), (2, 'Alex'), (3, 'Jo')
), orders(id, customer_id, cents) AS (
  VALUES (10, 1, 1200), (11, 1, 800), (12, 2, 900)
)
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

Run:

python3 run-sql.py no-orders.sql

Output:

3|Jo

The left join first keeps everyone; the filter then selects only rows with no matched order. Test a right-side column that cannot be missing on a real order. In a real schema, an integer primary-key order ID is a useful choice. Testing a nullable note field could accidentally label an existing order as absent.

This is an absence report, so Jo appears once. Removing the filter would bring back the matching orders too, including both of Mina’s rows.

See SQLite’s join processing reference. Always write the match condition explicitly and use qualified column names when the tables share names such as id.

Clean up

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

rm -- run-sql.py matching-orders.sql optional-orders.sql no-orders.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.