5
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

【SQLite3】テーブルの結合(JOIN句)

Posted at

内部結合:INNER JOIN

内部結合とは

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

INNER JOIN句の使い方

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

内部結合の実践

2つのテーブルを用意する

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         
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       

テーブルの結合

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       
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         
取得するカラムを指定しての内部結合
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つのテーブルに同じ名前のカラムがあるのにテーブル名を省略した場合は次のようなエラーが出る。

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句の使い方

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

外部結合の実践

2つのテーブルを用意する

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         
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       

テーブルの結合

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       
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         
取得するデータのカラムを指定する
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句の使い方

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

交差結合の実践

2つのテーブルを用意する

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     
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     

テーブルの結合

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/)
5
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?