import sqlite3
conn = sqlite3.connect(“:memory:”)
cur = conn.cursor()
# テーブル作成
cur.executescript(“””
CREATE TABLE orders (
id INTEGER,
customer_id INTEGER,
amount INTEGER
);
CREATE TABLE customers (
id INTEGER,
name TEXT,
country TEXT
);
INSERT INTO orders VALUES (1, 101, 5000);
INSERT INTO orders VALUES (2, 102, 3000);
INSERT INTO orders VALUES (3, 103, 8000);
INSERT INTO orders VALUES (4, 999, 1000); — 存在しない顧客
INSERT INTO customers VALUES (101, ‘田中’, ‘日本’);
INSERT INTO customers VALUES (102, ‘鈴木’, ‘日本’);
INSERT INTO customers VALUES (103, ‘John’, ‘USA’);
INSERT INTO customers VALUES (104, ‘李’, ‘中国’); — 注文なし顧客
“””)
print(“=” * 50)
print(“【JOIN】横につなぐ”)
print(“=” * 50)
print(“\n— INNER JOIN（両方に存在するもの）—“)
cur.execute(“””
SELECT o.id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
“””)
for row in cur.fetchall():
print(row)
print(“\n— LEFT JOIN（ordersを全部残す）—“)
cur.execute(“””
SELECT o.id, c.name, o.amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
“””)
for row in cur.fetchall():
print(row)
print(“\n— RIGHT JOIN相当（customersを全部残す）—“)
cur.execute(“””
SELECT o.id, c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
“””)
for row in cur.fetchall():
print(row)
print(“\n” + “=” * 50)
print(“【UNION】縦につなぐ”)
print(“=” * 50)
print(“\n— UNION（重複除去）—“)
cur.execute(“””
SELECT name, country FROM customers WHERE country = ‘日本’
UNION
SELECT name, country FROM customers WHERE country = ‘USA’
“””)
for row in cur.fetchall():
print(row)
print(“\n— UNION ALL（重複そのまま）—“)
cur.execute(“””
SELECT country FROM customers
UNION ALL
SELECT country FROM customers
“””)
for row in cur.fetchall():
print(row)
conn.close()
