INNER JOIN の内部処理(概説)

INNER JOIN の内部処理

1. CROSS JOIN で集合同士の組合わせ(順列テーブル)を作成する
2. ON 条件に合致する集合を ↑ から、一つ一つ走査する
3. 条件合致した集合を抽出し、これを結果の集合とする

前提テーブルとデータ(RDBMS == PostgreSQL)

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

「INNER JOIN 結果 SQL」

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
INNER JOIN products AS PROD
ON VEND.vendor_id = PROD.vendor_id;

 vendor_name | product_id | product_name 
-------------+------------+-------------
 mytech      |          1 | keyboard
 mytech      |          2 | mouse
 yourgeek    |          1 | cpu

各プロセス

(プロセス分解のために「INNER JOIN 結果 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 条件に合致するものを CROSS JOIN の集合から、
一つ一つ走査する

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. 条件合致した集合を抽出し、これを結果の集合とする

 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           

---

great celko