Use CROSS JOIN deliberately for a small option matrix

By Batu · English technical notes

For a small SQLite option matrix, use CROSS JOIN when every size must be paired with every color. With sizes S, M and colors red, blue, the result must contain four pairs.

The first query demonstrates an accidental condition: joining the two code columns requires a size code to equal a color code. None do, so the result is empty. This is a useful contrast because an ordinary join condition often looks plausible while quietly answering a matching question instead of a combinations question. The corrected query explicitly uses CROSS JOIN, then orders both columns to make the exact four-pair assertion stable.

A cross join is a Cartesian product. If there are N size rows and M color rows, the query returns N × M rows, so this pattern should be intentional and bounded. It is suitable for a small, known option set, but it can grow quickly as either input grows. Also note SQLite’s documented special optimizer treatment for CROSS JOIN: it can prevent table reordering. That is not needed to prove the matrix here; the operator is used because its result matches the model.

The code uses only Python’s standard-library sqlite3 module and a :memory: fixture, with no newer Python-only API required. SQLite SELECT documentation Python sqlite3 documentation

AI assistance disclosure: this synthetic example and explanation were prepared with AI assistance.

Example

import sqlite3


def pairs(connection, sql):
    return [tuple(row) for row in connection.execute(sql)]


connection = sqlite3.connect(":memory:")
connection.executescript(
    """
    CREATE TABLE sizes (code TEXT);
    CREATE TABLE colors (code TEXT);
    """
)
connection.executemany("INSERT INTO sizes VALUES (?)", [("S",), ("M",)])
connection.executemany("INSERT INTO colors VALUES (?)", [("red",), ("blue",)])

schema = [row[0] for row in connection.execute(
    "SELECT sql FROM sqlite_master WHERE type = 'table' ORDER BY name"
)]
size_seed = [row[0] for row in connection.execute("SELECT code FROM sizes ORDER BY code")]
color_seed = [row[0] for row in connection.execute("SELECT code FROM colors ORDER BY code")]
naive = pairs(
    connection,
    "SELECT sizes.code, colors.code FROM sizes "
    "JOIN colors ON sizes.code = colors.code ORDER BY 1, 2",
)
corrected = pairs(
    connection,
    "SELECT sizes.code, colors.code FROM sizes "
    "CROSS JOIN colors ORDER BY 1, 2",
)

assert naive == []
assert corrected == [("M", "blue"), ("M", "red"), ("S", "blue"), ("S", "red")]
print("schema:", schema)
print("seed:", size_seed, color_seed)
print("matching join:", naive)
print("CROSS JOIN:", corrected)

Expected output:

schema: ['CREATE TABLE colors (code TEXT)', 'CREATE TABLE sizes (code TEXT)']
seed: ['M', 'S'] ['blue', 'red']
matching join: []
CROSS JOIN: [('M', 'blue'), ('M', 'red'), ('S', 'blue'), ('S', 'red')]