SQL JOIN実習 | 実行結果
UTC: 2026-10-07T22:11:51.556754+00:00 / JST: 2026-10-08T07:11:51.556769+09:00
Python 3.12.14 / SQLite 3.53.1 / :memory: / foreign_keys=ON

=== base ===

[orders]
order_id | amount_yen
101 | 1200
102 | 1200
103 | 800

[items]
item_id | order_id | label
1 | 101 | Notebook
2 | 101 | Pen
3 | 102 | Folder

[base_totals]
order_count | amount_yen
3 | 3200

[left_rows]
order_id | amount_yen | item_id
101 | 1200 | 1
101 | 1200 | 2
102 | 1200 | 3
103 | 800 | NULL

[left_totals]
joined_rows | item_count | order_count | amount_yen | distinct_amount_yen
4 | 3 | 3 | 4400 | 2000

[inner_rows]
order_id | amount_yen | item_id
101 | 1200 | 1
101 | 1200 | 2
102 | 1200 | 3

[inner_totals]
joined_rows | order_count | amount_yen
3 | 2 | 3600

[grouped_wrong]
order_id | item_count | wrong_amount_yen
101 | 2 | 2400
102 | 1 | 1200
103 | 0 | 800

[distinct_all]
order_id | amount_yen | item_id
101 | 1200 | 1
101 | 1200 | 2
102 | 1200 | 3
103 | 800 | NULL

[preaggregate]
order_id | item_count
101 | 2
102 | 1

[correct_rows]
order_id | amount_yen | item_count
101 | 1200 | 2
102 | 1200 | 1
103 | 800 | 0

[correct_totals]
order_count | amount_yen | item_count
3 | 3200 | 3

=== added104 ===

[orders]
order_id | amount_yen
101 | 1200
102 | 1200
103 | 800
104 | 600

[items]
item_id | order_id | label
1 | 101 | Notebook
2 | 101 | Pen
3 | 102 | Folder
4 | 104 | Marker
5 | 104 | Paper

[base_totals]
order_count | amount_yen
4 | 3800

[left_rows]
order_id | amount_yen | item_id
101 | 1200 | 1
101 | 1200 | 2
102 | 1200 | 3
103 | 800 | NULL
104 | 600 | 4
104 | 600 | 5

[left_totals]
joined_rows | item_count | order_count | amount_yen | distinct_amount_yen
6 | 5 | 4 | 5600 | 2600

[inner_rows]
order_id | amount_yen | item_id
101 | 1200 | 1
101 | 1200 | 2
102 | 1200 | 3
104 | 600 | 4
104 | 600 | 5

[inner_totals]
joined_rows | order_count | amount_yen
5 | 3 | 4800

[grouped_wrong]
order_id | item_count | wrong_amount_yen
101 | 2 | 2400
102 | 1 | 1200
103 | 0 | 800
104 | 2 | 1200

[distinct_all]
order_id | amount_yen | item_id
101 | 1200 | 1
101 | 1200 | 2
102 | 1200 | 3
103 | 800 | NULL
104 | 600 | 4
104 | 600 | 5

[preaggregate]
order_id | item_count
101 | 2
102 | 1
104 | 2

[correct_rows]
order_id | amount_yen | item_count
101 | 1200 | 2
102 | 1200 | 1
103 | 800 | 0
104 | 600 | 2

[correct_totals]
order_count | amount_yen | item_count
4 | 3800 | 5

PASS: 24 queries
SQLiteのみ実行。MySQL未実行。実務DB・外部サービスへ接続しない。
