A customer report can lose the people who have not placed an order, even though their records are still in the database. The difference often comes from the join and the location of a filter. This SQL JOINs example makes that behavior visible with four fictional customers and four orders.
You will run INNER JOIN, LEFT JOIN, two paid-order filters, grouped totals and a query for customers without orders. Python is only the runner; the queries themselves are SQL. Download the complete example ZIP, including the runner, tests, README and expected output.
Set up a small disposable database
Use Python 3.10 or later with its sqlite3 module. No pip packages, database server, account or persistent database file are needed. The checks for this article used Python 3.12.14 and SQLite 3.53.1 on Windows; other versions and database engines were not separately tested.
python --version
python -c "import sqlite3; print(sqlite3.sqlite_version)"Extract the ZIP and open a terminal inside sql-joins-shop. On Windows, use py -3 if that selects your Python 3 installation; on systems using python3, substitute that command. If importing sqlite3 fails, use a Python distribution that includes the extension. The Python sqlite3 reference documents the connection and execution APIs used by the runner.
Understand the fixture before joining it
Ada has one paid order for 5000 sample cents and one pending order for 2000. Ben has one paid order for 3500. Cara has no order. Dina has one pending order for 1000. These integers are teaching data, not current prices. Order IDs are unique, and customer_id points to a customer.
Predict the report before running it: should Cara appear, and should Ada occupy one row or two? Write those expectations down. They give you a useful way to judge a result instead of accepting any output that happens to look like a table.
Save the complete runner as demo.py
"""A disposable SQLite shop fixture: Python 3.10+, no packages or files."""
import argparse
import sqlite3
QUERIES = {
"inner": """SELECT c.name, o.id, o.status
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id""",
"left": """SELECT c.name, o.id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id""",
"paid-on": """SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id, o.id""",
"paid-where": """SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id""",
"totals": """SELECT c.name, COUNT(o.id) AS order_count,
COALESCE(SUM(o.amount_cents), 0) AS total_cents
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id""",
"unmatched": """SELECT 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""",
}
def make_database():
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
status TEXT NOT NULL CHECK (status IN ('paid', 'pending')),
amount_cents INTEGER NOT NULL CHECK (amount_cents >= 0)
);
""")
db.executemany("INSERT INTO customers VALUES (?, ?)", [
(1, "Ada"), (2, "Ben"), (3, "Cara"), (4, "Dina")
])
db.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", [
(101, 1, "paid", 5000), (102, 1, "pending", 2000),
(103, 2, "paid", 3500), (104, 4, "pending", 1000)
])
db.commit()
return db
def query(db, name):
# The name selects a fixed query, never SQL supplied by the caller.
return db.execute(QUERIES[name]).fetchall()
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument("--case", choices=list(QUERIES), help="Run one query")
args = parser.parse_args()
db = make_database()
try:
for name in ([args.case] if args.case else QUERIES):
print(f"[{name}]")
for row in query(db, name):
print(" | ".join("NULL" if v is None else str(v) for v in row))
finally:
db.close()
if __name__ == "__main__":
main()
The script creates a fresh in-memory database, inserts the fixture and closes the connection after printing results. Foreign-key checking is explicitly enabled, following SQLite's foreign-key documentation. The source uses fixed schema/query text and parameterized inserts; the command-line option selects a known query name.
Run all six queries
python demo.pyThe expected output is shown below and included as expected-output.txt. NULL is the runner's display of Python None. Every query specifies an ordering so the fixture can be compared consistently.
[inner]
Ada | 101 | paid
Ada | 102 | pending
Ben | 103 | paid
Dina | 104 | pending
[left]
Ada | 101 | paid
Ada | 102 | pending
Ben | 103 | paid
Cara | NULL | NULL
Dina | 104 | pending
[paid-on]
Ada | 101
Ben | 103
Cara | NULL
Dina | NULL
[paid-where]
Ada | 101
Ben | 103
[totals]
Ada | 2 | 7000
Ben | 1 | 3500
Cara | 0 | 0
Dina | 1 | 1000
[unmatched]
Cara
Read the differences in the returned rows
In this fixture, inner returns four rows: each order paired with its customer. Ada appears twice because she has two orders. left adds Cara with empty order fields, producing five rows. Repeating a customer name here is useful information about the one-to-many relationship; adding DISTINCT merely to hide it would obscure the report's detail.
The paid-on query retains Cara and Dina with NULL order IDs. The paid-where query returns only Ada and Ben. For the underlying outer-join and filter rules, see the SQLite SELECT reference. Choose the query whose output matches the question: all customers with any paid orders, or only customers who have paid orders.
This is a useful debugging exercise when a dashboard total looks too small. Compare the customer IDs before and after each query change. If the customer list shrinks, inspect the filter and the intended report scope before changing seed data or adding rows to compensate.
Count orders and retain zero-order customers
The totals query returns one row per customer. For Cara, COUNT(o.id) produces zero; COUNT(*) would count the preserved result row and misleadingly report one. COALESCE turns her empty sum into zero. The SQLite aggregate-function reference explains the count and sum behavior.
Ada's total is 7000 because the query includes pending orders as well as paid ones. Do not label this column paid revenue. Name report fields for the exact values they include, especially when an order's status can change later. The integer convention avoids a display-formatting distraction, but it is not a full accounting design.
Find customers without an order
python demo.py --case unmatchedThe result is Cara. The query tests the joined order primary key with IS NULL; it does not compare a nullable description field. This makes the check refer to the absence of a matched order rather than a matched order with missing descriptive data.
Run the checks and try a useful error case
python -m unittest -v
python demo.py --case unknownAll ten tests passed in the recorded environment. They check the exact inner/left rows, both paid filters, totals, unmatched customers, an empty orders table, rejected foreign keys, rejected negative amounts and CLI success/invalid-choice behavior. The unknown choice prints argparse usage and exits with status 2; it never becomes executable SQL.
The empty-orders test is particularly useful when adapting this fixture. It verifies that an inner report becomes empty while customer totals still show four zero rows. Keep that case alongside the normal sample so a later change cannot silently remove customers without activity.
Adapt the lesson carefully
Start by changing one fixture row and predicting the new output. Then change one query and run the tests. A missing matching condition can multiply rows; a filter in the wrong place can remove customers you expected to retain. Diagnose those differences before connecting a real database.
This example does not test MySQL, PostgreSQL, production data volumes or query performance. If you later add a request parameter to choose a customer, pass it as a bound value rather than interpolating SQL. Our PHP PDO prepared-statements example shows that separate input/value boundary in a different runnable fixture.
Comments (0)
Join the discussion