INNER JOIN の内部処理(概説)
INNER JOIN の内部処理
- CROSS JOIN で集合同士の組合わせ(順列テーブル)を作成する
- ON 条件に合致する集合を ↑ から、一つ一つ走査する
- 条件合致した集合を抽出し、これを結果の集合とする
前提テーブルとデータ(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