الربط وتعددية المطابقة وتغير وحدة السجل

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.executescript("""
        CREATE TABLE customers(id INTEGER PRIMARY KEY);
        CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
    """)
    db.executemany("INSERT INTO customers VALUES (?)", [(1,), (2,), (3,)])
    db.executemany("INSERT INTO orders VALUES (?, ?, ?)",
                   [(10, 1, 20), (11, 1, 30), (12, 2, 15)])
    rows = db.execute("""SELECT c.id, o.id, o.amount
                         FROM customers AS c LEFT JOIN orders AS o
                           ON o.customer_id = c.id
                         ORDER BY c.id, o.id""").fetchall()
    for row in rows:
        print(row)
    print("rows", len(rows))

(1, 10, 20)
(1, 11, 30)
(2, 12, 15)
(3, None, None)
rows 4

يظهر العميل1 مرتين والثاني مرة والثالث مرة بحقول طلب مفقودة؛ وتفسر الوحدة الصفوف الأربعة.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.executescript("""
        CREATE TABLE orders(id INTEGER PRIMARY KEY, amount INTEGER);
        CREATE TABLE lines(id INTEGER PRIMARY KEY, order_id INTEGER);
        CREATE TABLE payments(id INTEGER PRIMARY KEY, order_id INTEGER);
        INSERT INTO orders VALUES (1, 20);
        INSERT INTO lines VALUES (1, 1), (2, 1);
        INSERT INTO payments VALUES (1, 1), (2, 1), (3, 1);
    """)
    joined = db.execute("""SELECT COUNT(*), SUM(o.amount) FROM orders AS o
                            JOIN lines AS l ON l.order_id = o.id
                            JOIN payments AS p ON p.order_id = o.id""").fetchone()
    original = db.execute("SELECT COUNT(*), SUM(amount) FROM orders").fetchone()
    matched = db.execute("""WITH a(k) AS (VALUES (NULL), (1)),
                                 b(k) AS (VALUES (NULL), (1))
                            SELECT COUNT(*) FROM a JOIN b ON a.k = b.k""").fetchone()[0]
    print("fanout", *joined)
    print("order_grain", *original)
    print("sql_null_matches", matched)

fanout 6 120
order_grain 1 20
sql_null_matches 1

ينتج بندان مضروبان في ثلاث دفعات ست نسخ من20. لا يطابق ربط المساواة للمفاتيح الاختيارية إلا المفتاح المعروف1.