Red > Green > Refactor > Red

cycle is based on desire

LEFT OUTER JOIN 内部処理(概説)(思念中)

RIGHT もやることは変わらない

LEFT OUTER JOIN の内部処理

  1. CROSS JOIN で集合の組合せ(順列テーブル) を作成する
  2. ON 条件に合致する集合を ↑ から走査し、合致すればその集合を保持する
  3. 条件に合致しなければ(FALSE か UNKNOWN) [X] を実行

[X]

  1. 保存対象のテーブルの列を保持
  2. 非 保存対象のテーブルの列を全て NULL にする
  3. 重複行を削除

前提テーブルとデータ

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 への絞り込みが追いつかない
  • ↓リンクが参考になりそうだが、うまく言語化できない

SQL LEFT JOIN Keyword