LEFT OUTER JOIN 内部処理(概説)(思念中)
RIGHT もやることは変わらない
LEFT OUTER JOIN の内部処理
- CROSS JOIN で集合の組合せ(順列テーブル) を作成する
- ON 条件に合致する集合を ↑ から走査し、合致すればその集合を保持する
- 条件に合致しなければ(FALSE か UNKNOWN) [X] を実行
[X]
- 保存対象のテーブルの列を保持
- 非 保存対象のテーブルの列を全て NULL にする
- 重複行を削除
前提テーブルとデータ
CREATE TABLE vendors ( vendor_id INTEGER, name text ); select * from vendors; vendor_id | name -----------+------------ 1 | mytech 2 | yourgeek 3 | whosepower
CREATE TABLE products ( vendor_id INTEGER, product_id INTEGER, name text ); select * from products vendor_id | product_id | name -----------+------------+---------- 1 | 1 | keyboard 1 | 2 | mouse 2 | 1 | cpu
「LEFT OUTER JOIN の結果」
保存対象のテーブル: vendors
非 保存対象のテーブル: products
SELECT VEND.name as vendor_name, VEND.vendor_id, PROD.vendor_id, PROD.product_id AS product_id, PROD.name as product_name FROM vendors as VEND LEFT OUTER JOIN products AS PROD ON VEND.vendor_id = PROD.vendor_id; vendor_name | vendor_id | vendor_id | product_id | product_name -------------+-----------+-----------+------------+-------------- mytech | 1 | 1 | 1 | keyboard mytech | 1 | 1 | 2 | mouse yourgeek | 2 | 2 | 1 | cpu whosepower | 3 | | |
各プロセス
(プロセス分解のために SQL を変更した箇所あり)
1. CROSS JOIN で集合の組合せ(順列テーブル) を作成する
SELECT VEND.name as vendor_name, VEND.vendor_id, PROD.vendor_id, PROD.product_id AS product_id, PROD.name as product_name FROM vendors as VEND CROSS JOIN products as PROD; vendor_name | vendor_id | vendor_id | product_id | product_name -------------+-----------+-----------+------------+-------------- mytech | 1 | 1 | 1 | keyboard mytech | 1 | 1 | 2 | mouse mytech | 1 | 2 | 1 | cpu yourgeek | 2 | 1 | 1 | keyboard yourgeek | 2 | 1 | 2 | mouse yourgeek | 2 | 2 | 1 | cpu whosepower | 3 | 1 | 1 | keyboard whosepower | 3 | 1 | 2 | mouse whosepower | 3 | 2 | 1 | cpu
2. ON 条件に合致する集合を ↑ から走査し、
合致すればその集合を保持する
ON VEND.vendor_id = PROD.vendor_id; vendor_name | vendor_id | vendor_id | product_id | product_name -------------+-----------+-----------+------------+-------------- mytech | 1 | 1 | 1 | keyboard << mytech | 1 | 1 | 2 | mouse << mytech | 1 | 2 | 1 | cpu yourgeek | 2 | 1 | 1 | keyboard yourgeek | 2 | 1 | 2 | mouse yourgeek | 2 | 2 | 1 | cpu << whosepower | 3 | 1 | 1 | keyboard whosepower | 3 | 1 | 2 | mouse whosepower | 3 | 2 | 1 | cpu
3. 条件に合致しなければ(FALSE か UNKNOWN)
(※ 1)
ON VEND.vendor_id = PROD.vendor_id; vendor_name | vendor_id | vendor_id | product_id | product_name -------------+-----------+-----------+------------+-------------- mytech | 1 | 1 | 1 | keyboard << mytech | 1 | 1 | 2 | mouse << yourgeek | 2 | 2 | 1 | cpu << whosepower | 3 | 1 | 1 | keyboard <<<< whosepower | 3 | 1 | 2 | mouse <<<< whosepower | 3 | 2 | 1 | cpu <<<<
3-1. 保存対象のテーブルの列を保持
3-2. 非 保存対象のテーブルの列を全て NULL にする
vendor_name | vendor_id | vendor_id | product_id | product_name -------------+-----------+-----------+------------+-------------- mytech | 1 | 1 | 1 | keyboard << mytech | 1 | 1 | 2 | mouse << yourgeek | 2 | 2 | 1 | cpu << whosepower | 3 | | | <<<< whosepower | 3 | | | <<<< whosepower | 3 | | | <<<<
3-3. 重複行を削除
ON VEND.vendor_id = PROD.vendor_id; vendor_name | vendor_id | vendor_id | product_id | product_name -------------+-----------+-----------+------------+-------------- mytech | 1 | 1 | 1 | keyboard << mytech | 1 | 1 | 2 | mouse << yourgeek | 2 | 2 | 1 | cpu << whosepower | 3 | | | <<<<
—————
- 2 -> 3 への絞り込みが追いつかない
- ↓リンクが参考になりそうだが、うまく言語化できない