【SQLite3】テーブルの結合（JOIN句）


# 内部結合:INNER JOIN
## 内部結合とは

内部結合とは２つのテーブルで結合の対象となるカラムを指定し、それぞれに同じ値が格納されているデータを結合して取得するもの。

## INNER JOIN句の使い方

```sql:書式
SELECT (取得するカラム) FROM テーブル名1 INNER JOIN テーブル名2 ON (結合条件);
```

## 内部結合の実践
### 2つのテーブルを用意する

```sql:1つめのテーブル
CREATE TABLE staff(id INTEGER, name TEXT, dept INTEGER);

INSERT INTO staff VALUES(1, 'Suzuki', 1);
INSERT INTO staff VALUES(2, 'Endou', 3);
INSERT INTO staff VALUES(3, 'Katou', 1);
INSERT INTO staff VALUES(4, 'Yamada', 2);
INSERT INTO staff VALUES(5, 'Takahashi', 4);
INSERT INTO staff VALUES(6, 'Honda', 3);

SELECT * FROM staff;
id          name        deptid    
----------  ----------  ----------
1           Suzuki      1         
2           Endou       3         
3           Katou       1         
4           Yamada      2         
5           Takahashi   4         
6           Honda       3         
```

```sql:2つめのテーブル
CREATE TABLE dept(id INTEGER, name TEXT);

INSERT INTO dept(1, 'Sales');
INSERT INTO dept(2, 'Manage');
INSERT INTO dept(3, 'Dev');

SELECT * FROM dept;

id          name      
----------  ----------
1           Sales     
2           Manage    
2           Dev       
```

### テーブルの結合

```sql:staffテーブルにdeptテーブルを内部結合
SELECT * FROM staff INNER JOIN dept ON staff.deptid = dept.id;

id          name        deptid      id          name      
----------  ----------  ----------  ----------  ----------
1           Suzuki      1           1           Sales     
2           Endou       3           3           Dev       
3           Katou       1           1           Sales     
4           Yamada      2           2           Manage    
6           Honda       3           3           Dev       
```

```sql:deptテーブルにstaffテーブルを内部結合
SELECT * FROM dept INNER JOIN staff ON dept.id = staff.deptid;

id          name        id          name        deptid    
----------  ----------  ----------  ----------  ----------
1           Sales       1           Suzuki      1         
1           Sales       3           Katou       1         
2           Manage      4           Yamada      2         
```

```sql:取得するカラムを指定しての内部結合
SELECT staff.id, staff.name, dept.name FROM staff INNER JOIN dept ON staff.deptid = dept.id;

id          name        name      
----------  ----------  ----------
1           Suzuki      Sales     
2           Endou       Dev       
3           Katou       Sales     
4           Yamada      Manage    
6           Honda       Dev       
```

### テーブル名を省略した書式
次の条件を満たしている場合、テーブル名を省略してSELECT文を書くことができる。
- どちらか一方のテーブルにしか含まれていないこと
- テーブル名を省略してもどちらのテーブルのカラムかわかる場合

2つのテーブルに同じ名前のカラムがあるのにテーブル名を省略した場合は次のようなエラーが出る。

```sql:2つのテーブルに同じ名前のカラムがあるのにテーブル名を省略した場合
SELECT staff.id, name, deptid, dept.name FROM staff INNER JOIN dept ON staff.deptid = dept.id;

Error: ambiguous column name: name

```

---

# 外部結合:OUTER JOIN
## 外部結合とは

2つのテーブルを結合してデータを取得する方法の中で指定したそれぞれのテーブルのカラムの値が一致するデータにだけでなく、どちらかのテーブルにだけデータがある場合も合わせて取得する方法。

※内部結合と違い```FROM```の後のテーブル名を入れ替えると抽出される結果が異なります。

## SQLite3での外部結合

一般的に外部結合には下記の3種類があります。

- 左外部結合：SELECT文の```FROM```以降に記述された左側のテーブルにしかないデータも取得する方法。
- 右外部結合：SELECT文の```FROM```以降に記述された右側のテーブルにしかないデータも取得する方法。
- 完全外部結合：SELECT文の```FROM```以降に記述された左側のテーブルにしかないデータと右側のテーブルにしかないデータも取得する方法。

SQLite3では、左外部結合（LEFT OUTER JOIN）しかサポートされていない。
右外部結合（RIGHT OUTER JOIN）や完全外部結合（FULL OUTER JOIN）を行おうとすると、
```Error:RIGHT and FULL OUTER JOINs are not currently supported```というエラーが出る。

## OUTER JOIN句の使い方

```sql
SELECT (取得するカラム) FROM テーブル名1 LEFT OUTER JOIN テーブル名2 ON (結合条件);
```

## 外部結合の実践
### 2つのテーブルを用意する
```sql:1つめのテーブル
CREATE TABLE staff(id INTEGER, name TEXT, dept INTEGER);

INSERT INTO staff VALUES(1, 'Suzuki', 1);
INSERT INTO staff VALUES(2, 'Endou', 3);
INSERT INTO staff VALUES(3, 'Katou', 1);
INSERT INTO staff VALUES(4, 'Yamada', 2);
INSERT INTO staff VALUES(5, 'Takahashi', 4);
INSERT INTO staff VALUES(6, 'Honda', 3);

SELECT * FROM staff;
id          name        deptid    
----------  ----------  ----------
1           Suzuki      1         
2           Endou       3         
3           Katou       1         
4           Yamada      2         
5           Takahashi   4         
6           Honda       3         
```

```sql:2つめのテーブル
CREATE TABLE dept(id INTEGER, name TEXT);

INSERT INTO dept(1, 'Sales');
INSERT INTO dept(2, 'Manage');
INSERT INTO dept(3, 'Dev');

SELECT * FROM dept;

id          name      
----------  ----------
1           Sales     
2           Manage    
2           Dev       
```

### テーブルの結合

```sql
SELECT * FROM staff LEFT OUTER JOIN dept ON staff.deptid = dept.id;

id          name        deptid      id          name      
----------  ----------  ----------  ----------  ----------
1           Suzuki      1           1           Sales     
2           Endou       3           3           Dev       
3           Katou       1           1           Sales     
4           Yamada      2           2           Manage    
5           Takahashi   4                                 
6           Honda       3           3           Dev       
```

```sql:staffテーブルとdeptテーブルを逆にして外部結合
SELECT * FROM dept LEFT OUTER JOIN staff ON dept.id = staff.deptid;

id          name        id          name        deptid    
----------  ----------  ----------  ----------  ----------
1           Sales       1           Suzuki      1         
1           Sales       3           Katou       1         
2           Manage      4           Yamada      2         
3           Dev         2           Endou       3         
3           Dev         6           Honda       3         
```

```sql:取得するデータのカラムを指定する
SELECT staff.id, staff.name, dept.name FROM staff LEFT OUTER JOIN dept ON staff.deptid= dept.id;

id          name        name      
----------  ----------  ----------
1           Suzuki      Sales     
2           Endou       Dev       
3           Katou       Sales     
4           Yamada      Manage    
5           Takahashi             
6           Honda       Dev       
```

---
# 交差結合:CROSS JOIN
## 交差結合とは

2つのテーブルのデータの組み合わせを取得するもの。
SELECT文の```FROM```以降の左側のテーブルのデータ1つにつき右側のテーブルのデータを最初から最後まで1つずつ結合する。

左側のテーブルのデータ = n個、右側のテーブルのデータ = m個だった場合、交差結合したデータの個数がn✕m個となり、場合によっては非常に多くなるので注意が必要。

## CROSS JOIN句の使い方

```sql
SELECT (取得するカラム) FROM テーブル名1 CROSS JOIN テーブル名2 ON (結合条件);
```

## 交差結合の実践
### 2つのテーブルを用意する

```sql:1つめのテーブル
CREATE TABLE product(id INTEGER, name TEXT);

INSERT INTO product VALUES(1, 'Desk');
INSERT INTO product VALUES(2, 'Light');
INSERT INTO product VALUES(3, 'Chair');

SELECT * FROM product;

id          name      
----------  ----------
1           Desk      
2           Light     
3           Chair     
```

```sql:2つめのテーブル
CREATE TABLE color(id INTEGER, name TEXT);

INSERT INTO color VALUES(1, 'Red');
INSERT INTO color VALUES(2, 'Blue');
INSERT INTO color VALUES(3, 'White');

SELECT * FROM color;

id          name      
----------  ----------
1           Red       
2           Blue      
3           White     
```

### テーブルの結合

````sql
SELECT * FROM product CROSS JOIN color;

id          name        id          name      
----------  ----------  ----------  ----------
1           Desk        1           Red       
1           Desk        2           Blue      
1           Desk        3           White     
2           Light       1           Red       
2           Light       2           Blue      
2           Light       3           White     
3           Chair       1           Red       
3           Chair       2           Blue      
3           Chair       3           White     
```

```sql:取得するデータのカラムを指定する
SELECT product.id, product.name, color.name FROM product CROSS JOIN color;

id          name        name      
----------  ----------  ----------
1           Desk        Red       
1           Desk        Blue      
1           Desk        White     
2           Light       Red       
2           Light       Blue      
2           Light       White     
3           Chair       Red       
3           Chair       Blue      
3           Chair       White     
```


---

# 自然結合:NATURALキーワード
## 自然結合とは

内部結合や外部結合の場合、結合条件で指定したテーブルのカラムを比較してデータを結合した。
```NATURAL```キーワードを指定するとカラム名を指定しなくても2つのテーブルで同名のカラムを使って結合を行える。

## NATURALキーワードの使い方

- 内部結合の場合
  
  ```sql
  SELECT (取得するカラム) FROM テーブル名1 NATURAL INNER JOIN テーブル名2;
  ```

- 外部結合の場合

  ```sql
  SELECT (取得するカラム) FROM テーブル名1 NATURAL LEFT OUTER JOIN テーブル名2;
  ```

## 自然結合の実践
### 2つのテーブルを用意する

```sql:1つめのテーブル
CREATE TABLE staff(id INTEGER, name TEXT, dept INTEGER);

INSERT INTO staff VALUES(1, 'Suzuki', 1);
INSERT INTO staff VALUES(2, 'Endou', 3);
INSERT INTO staff VALUES(3, 'Katou', 1);
INSERT INTO staff VALUES(4, 'Yamada', 2);
INSERT INTO staff VALUES(5, 'Takahashi', 4);
INSERT INTO staff VALUES(6, 'Honda', 3);

SELECT * FROM staff;
id          name        deptid    
----------  ----------  ----------
1           Suzuki      1         
2           Endou       3         
3           Katou       1         
4           Yamada      2         
5           Takahashi   4         
6           Honda       3         
```

```sql:2つめのテーブル
CREATE TABLE dept(deptid INTEGER, deptname TEXT);
INSERT INTO dept(1, 'Sales');
INSERT INTO dept(2, 'Manage');
INSERT INTO dept(3, 'Dev');

SELECT * FROM dept;

deptid      deptname  
----------  ----------
1           Sales     
2           Manage    
3           Dev       
```

```sql:NATURALキーワードを用いた内部結合
SELECT * FROM staff NATURAL INNER JOIN dept;

id          name        deptid      deptname  
----------  ----------  ----------  ----------
1           Suzuki      1           Sales     
2           Endou       3           Dev       
3           Katou       1           Sales     
4           Yamada      2           Manage    
6           Honda       3           Dev       
```

```sql:NATURALキーワードを用いた外部結合
SELECT * FROM staff NATURAL LEFT OUTER JOIN dept;

id          name        deptid      deptname  
----------  ----------  ----------  ----------
1           Suzuki      1           Sales     
2           Endou       3           Dev       
3           Katou       1           Sales     
4           Yamada      2           Manage    
5           Takahashi   4                     
6           Honda       3           Dev       
```

---

# 自己結合
## 自己結合とは

内部結合や外部結合を行う時に自分自身のテーブルと結合すること。
自己結合では同じテーブルを使用するため、それぞれに別名を付けてSQLを記述する。

## 自己結合の使い方

```sql
SELECT 別名1.カラム名1, .... FROM テーブル名1 別名1 INNER JOIN テーブル名2 別名2 ON 別名1.カラム名1 = 別名2.カラム名2;
```

## 自己結合の実践
### 1つのテーブルを用意する

```sql
CREATE TABLE staff(id INTEGER, name TEXT, bossid INTEGER);

INSERT INTO staff VALUES(1, 'Yamada', 3);
INSERT INTO staff VALUES(2, 'Itou', 4);
INSERT INTO staff VALUES(3, 'Suzuki', 5);
INSERT INTO staff VALUES(4, 'Ueda', 3);
INSERT INTO staff VALUES(5, 'Kuroda', 0);

SELECT * FROM staff;

id          name        bossid    
----------  ----------  ----------
1           Yamada      3         
2           Itou        4         
3           Suzuki      5         
4           Ueda        3         
5           Kuroda      0         
```

### テーブルに別名を付けて結合する

```sql:内部結合
SELECT person.id, person.name, boss.name FROM staff person INNER JOIN staff boss ON person.bossid = boss.id;

id          name        name      
----------  ----------  ----------
1           Yamada      Suzuki    
2           Itou        Ueda      
3           Suzuki      Kuroda    
4           Ueda        Suzuki   
```

```sql:外部結合
SELECT person.id, person.name, boss.name FROM staff person LEFT OUTER JOIN staff boss ON person.bossid = boss.id;

id          name        name      
----------  ----------  ----------
1           Yamada      Suzuki    
2           Itou        Ueda      
3           Suzuki      Kuroda    
4           Ueda        Suzuki    
5           Kuroda                
```

---

### 本記事目次ページ

- [【データベース】SQLite3・JDBCまとめ](https://qiita.com/tsweblabo/items/8fa21f3e9e30885be034)

---

### 参考サイト

- [DBOnline 初心者でもわかりやすいサイトSQLite入門](https://www.dbonline.jp/sqlite/)