CROSS/INNER/OUTER joinの備忘録

# 概要
情報整理程度にしかSQL分を使わなくて、毎回joinについて、LEFTだーRIGHTだー、をすぐわすれるので、メモした。

Excelめんどくさいし、sqlite3にデータ入れて検索しよ、という程度のDBライトユーザの話。

- CSVファイル(data.sv)を、samba経由でsqlite3が使えるホストに置く
- 処理用データベースを開く
  - `$ sqlite3 newDB.db`
- インポートするデータに合わせた、テーブルを作る
  - `sqlite> create table newtable (...);`
- セパレータを変えてインポートする
  - `sqlite> .separator ,`
  - `sqlite> .import ./data.csv newtable`
- データをいじる
  - .mode columnや.headers on辺りで見やすくして、selectとか云々
- データ書き出し
  - `sqlite> .output ./output.csv`
  - `sqlite> select ....`
- samba経由でデータ改修して終わり。


## 確認用のデータ
sqlite3で alphabet, phonetic というテーブルを用意しています。

```
sqlite> .mode column
sqlite> .headers on
sqlite> .schema
CREATE TABLE phonetic ( id integer, name text);
CREATE TABLE alphabet ( id integer, name text);
sqlite> select * from alphabet;
id          name      
----------  ----------
1           A         
2           B         
3           C         
4           D         
sqlite> select * from phonetic;
id          name      
----------  ----------
1           Alpha     
2           Bravo     
3           Charlie   
sqlite> 
```

イメージはこれですね

```
sqlite> select a.name, a.id, p.name from alphabet a left outer join phonetic p on a.id=p.id;
name        id          name      
----------  ----------  ----------
A           1           Alpha     
B           2           Bravo     
C           3           Charlie   
D           4                     
sqlite> 
```

# 結合の種類

以下の結合から好きなのを選んで使おう

- CROSS JOIN
- INNER JOIN
- OUTER JOIN
  - LEFT OUTER JOIN
  - RIGHT OUTER JOIN (sqlite3未実装)
  - FULL OUTER JOIN (sqlite3未実装)

ONで結合の条件、WHEREで抽出の条件を書くよね。

## CROSS JOIN

選択されたもののすべての組み合わせが出てくる。
テーブルの列同士の比較。

```
sqlite> select a.name, p.name from alphabet a CROSS JOIN phonetic p;
name        name      
----------  ----------
A           Alpha     
A           Bravo     
A           Charlie   
B           Alpha     
B           Bravo     
B           Charlie   
C           Alpha     
C           Bravo     
C           Charlie   
D           Alpha     
D           Bravo     
D           Charlie   
sqlite>
```


テーブルのレコード単位での結合なので、ここでは alphabet(id,name)とphonetic(id,name)の塊での突合せになる。

```
sqlite> select * from alphabet a CROSS JOIN phonetic p;
id          name        id          name      
----------  ----------  ----------  ----------
1           A           1           Alpha     
1           A           2           Bravo     
1           A           3           Charlie   
2           B           1           Alpha     
2           B           2           Bravo     
2           B           3           Charlie   
3           C           1           Alpha     
3           C           2           Bravo     
3           C           3           Charlie   
4           D           1           Alpha     
4           D           2           Bravo     
4           D           3           Charlie   
sqlite> 
```

## INNER JOIN
キーとなる特定の列で結合し、結合できたものだけを得る。
キーが両方に存在しないものは、除外される。

- alphabet(4,"Delta")は、phoneticにid:4が無いので結合時に除外されてる。

```
sqlite> select * from alphabet a INNER JOIN phonetic p on a.id=p.id;
id          name        id          name      
----------  ----------  ----------  ----------
1           A           1           Alpha     
2           B           2           Bravo     
3           C           3           Charlie   
sqlite> 
```

CROSS JOINでキーが同じものを抽出した、と同じ。

```
sqlite> select * from alphabet a CROSS JOIN phonetic p where a.id=p.id;
id          name        id          name      
----------  ----------  ----------  ----------
1           A           1           Alpha     
2           B           2           Bravo     
3           C           3           Charlie   
sqlite> 
```

## OUTER JOIN
INNER JOINではキーが一致しない行は全て捨てられたが、OUTER JOINでは「存在しない場合はNULL」で検索できる。

- slqite3だとRIGHT/FULL JOINが使えないので、LEFT OUTER JOINのみ。

```
sqlite> select * from alphabet a LEFT OUTER JOIN phonetic p on a.id=p.id;
id          name        id          name      
----------  ----------  ----------  ----------
1           A           1           Alpha     
2           B           2           Bravo     
3           C           3           Charlie   
4           D                                 
sqlite> 
```

必要なものと価格表との突合せ、みたいな。

```
sqlite> select w.wants, a.price from 欲しいもの w left outer join amazonの金額 a on w.wants=a.product limit 5;
wants       price     
----------  ----------
時間                
お金                
ドローン     30000     
バイク       55000     
希望         999999999 
sqlite> 
```

# まとめ

普通は inner join か left outer join しか使わなくね？
まぁ、備忘録なんで。。