What you will learn
A customer list tells you who your customers are. An orders table tells you what they ordered. A join lets you ask a question that needs both, such as “How many paid orders does each customer have, including customers with zero?”
By the end, you will run an inner join, compare it with a left join, and count paid orders without losing customers who have zero. The synthetic dataset is small enough to check by hand.
Prerequisites: basic familiarity with SELECT and table columns, plus Python 3 with sqlite3 available. Python’s sqlite3 module can create an in-memory SQLite database; closing the connection discards it. This example was executed with Python 3.12.14 and SQLite 3.53.1.
Start with the meaning of a row
Our customers table contains Asha, Ben, and Chen. Each customer has a unique customer_id. Our orders table contains orders 101 and 102 for Asha, and order 103 for Ben. Asha has one paid order and one pending order; Ben’s order is paid. Chen has no orders.
The relationship is one-to-many: one customer can have several orders. Matching the customer_id columns produces one row for each matching customer–order pair. Asha therefore appears twice in the detailed result. Those rows represent two distinct orders.
An INNER JOIN returns matching pairs. A LEFT JOIN also retains unmatched rows from its left-hand table, filling the right-hand columns with NULL. Here, customers is the left-hand table. These behaviors follow SQLite’s SELECT documentation.
Before running anything, predict the row counts. The inner join should return three rows. The left join should return four because it also retains Chen.
Run the complete practice dataset
Save this as sql_joins.py in a practice folder and run python sql_joins.py. Use python3 or py instead if that is your computer’s Python command. SQLite executes the SQL inside the Python strings.
import sqlite3
SETUP = """
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status TEXT NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT INTO orders VALUES (101, 1, 'paid'), (102, 1, 'pending'), (103, 2, 'paid');
"""
QUERIES = {
"INNER JOIN": """
SELECT c.name, o.order_id, o.status
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;
""",
"LEFT JOIN": """
SELECT c.name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;
""",
"Paid orders per customer": """
SELECT c.name, COUNT(o.order_id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
""",
}
def main():
connection = sqlite3.connect(":memory:")
try:
connection.executescript(SETUP)
for label, query in QUERIES.items():
print(label)
for row in connection.execute(query):
print(row)
finally:
connection.close()
if __name__ == "__main__":
main()
You should see:
INNER JOIN
('Asha', 101, 'paid')
('Asha', 102, 'pending')
('Ben', 103, 'paid')
LEFT JOIN
('Asha', 101, 'paid')
('Asha', 102, 'pending')
('Ben', 103, 'paid')
('Chen', None, None)
Paid orders per customer
('Asha', 1)
('Ben', 1)
('Chen', 0)
Python displays SQL NULL as None. Chen’s None values therefore mean that the left join found no matching order. They do not represent an order with an empty status.
Count matches without losing zeroes
The last query answers our original question. Its ON condition requires both a matching customer ID and a paid order. Chen still gets a retained customer row, even though no order satisfies that condition.
COUNT(o.order_id) counts non-NULL order IDs. For Chen, that count is zero. COUNT(*) would count the retained row itself and incorrectly report one paid order. SQLite documents the distinction between COUNT(expression) and COUNT(*).
GROUP BY gathers each customer’s matched rows into one result row. Grouping by customer ID as well as name also avoids merging different customers who happen to share a name. ORDER BY makes the printed sequence explicit, which helps you compare repeat runs.
Two mistakes worth testing
First, move o.status = 'paid' out of ON and put it in a WHERE clause after the join. Chen disappears: the unmatched row does not pass the paid-status filter. Keep that condition in ON when your question requires all customers, including those with no paid orders.
Second, do not add DISTINCT merely because a name appears twice. Asha legitimately has two orders. Removing duplicates without understanding the row’s meaning can hide a mistaken join or discard useful detail. Check IDs and expected counts before making a report look tidier.
Practice and limits
Exercise: add customer (4, 'Dev') and order (104, 4, 'pending') to the INSERT statements. Predict the final query before running it. Dev should appear with zero paid orders, alongside Chen. The paid counts should be Asha 1, Ben 1, Chen 0, and Dev 0.
This compact schema is for learning. It omits production constraints, indexes, access controls, and query-performance analysis. Before adapting it, confirm which keys are unique and whether missing matches indicate a valid business state or a data problem. Small datasets make mistakes easier to spot.