はじめに
SQLはデータベースを操作するためのプログラムです。
プログラムをどのように分解するかは賛否あると思いますが、
ここでは以下の４つに分類して基本の構文をまとめます。
- 処理内容:検索/更新/挿入/削除のいずれの処理をおこなうか
- 対象テーブル:処理対象のテーブルと、テーブル間の関係
- 絞り込み条件:対象レコードをどのように絞り込むか
- 表示条件・計算など:定義された関数での計算や表示順など
このページでは対象テーブルを指定する基本構文をまとめます。
対象テーブルの指定には、メインとなるテーブルの指定、結合、集合の大きく３つの分類があります。
例として、年別の会員名簿(こんなデータの持ち方イケてないですけど)とステータスの区分を表す
合計3テーブルが存在するとして、各SQLの動きを解説します。
1年目会員名簿(MEMBER_LIST_1ST)
| ID | FAMIRY_NAME | FIRST_NAME | BIRTH_DAY | GENDER | STATUS_ID | NOTE |
|---|---|---|---|---|---|---|
| 0001 | 鈴木 | 太郎 | 1980/10/25 | M | 1 | |
| 0002 | 山田 | 太郎 | 1981/3/1 | M | 1 | |
| 0003 | 佐藤 | 長次郎 | 1980/4/2 | M | 1 | |
| 0004 | 鈴木 | 定子 | 1980/8/2 | F | 1 |
2年目会員名簿(MEMBER_LIST_2ND)
| ID | FAMIRY_NAME | FIRST_NAME | BIRTH_DAY | GENDER | STATUS_ID | NOTE |
|---|---|---|---|---|---|---|
| 0001 | 鈴木 | 太郎 | 1980/10/25 | M | 1 | |
| 0002 | 山田 | 太郎 | 1981/3/1 | M | 2 | |
| 0003 | 佐藤 | 長次郎 | 1980/4/2 | M | 1 | |
| 0004 | 鈴木 | 定子 | 1980/8/2 | F | 1 | |
| 0005 | 鈴本 | 花子 | 1980/12/14 | F | 1 |
ステータス区分(STATUS_DIV)
| STATUS_ID | STATUS_TEXT |
|---|---|
| 1 | 正規会員 |
| 2 | 退会 |
処理対象のメインテーブル(FROM)
SELECT * FROM MDMBER_LIST_1ST
"1年目会員名簿"からデータを"検索"する
特に条件も指定していないので、すべてのデータを取得する
| ID | FAMIRY_NAME | FIRST_NAME | BIRTH_DAY | GENDER | STATUS_ID | NOTE |
|---|---|---|---|---|---|---|
| 0001 | 鈴木 | 太郎 | 1980/10/25 | M | 1 | |
| 0002 | 山田 | 太郎 | 1981/3/1 | M | 1 | |
| 0003 | 佐藤 | 長次郎 | 1980/4/2 | M | 1 | |
| 0004 | 鈴木 | 定子 | 1980/8/2 | F | 1 |
結合(JOIN)
複数テーブルを対象に処理をおこなう場合は、テーブルを結合する
イメージとして、SQL文で先に記述されている順に左から並べている。
この並び順は結果表示の並び順と、外部結合のLEFT,RIGHTの関係で使われる。
内部結合(INNER JOIN)
結合条件(ON,AND)に合致するデータが各テーブルにあるレコードを結合して抽出する
*INNERを省略することも可能
キーは"AND"でつなげることで複数指定することができる。
もし、結合できないキーを持っている(もしくはキーを持っていない)データがあると、
そのデータは抽出されない。
SELECT * FROM MEMBER_LIST_2ND
INNER JOIN STATUS_DIV
ON MEMBER_LIST_2ND.STATUS_ID = STATUS_DIV.STATUS_ID
「2年目会員名簿」と「ステータス区分」のテーブルを
それぞれの"STATUS_ID"をキーにして結合する。
もし、何かのミスで「2年目会員名簿」のSTATUS_IDに"3"のデータがある場合、そのレコードは抽出されない。
| ID | FAMIRY_NAME | FIRST_NAME | BIRTH_DAY | GENDER | STATUS_ID | NOTE | STATUS_DIV.STATUS_ID | STATUS_TEXT |
|---|---|---|---|---|---|---|---|---|
| 0001 | 鈴木 | 太郎 | 1980/10/25 | M | 1 | 1 | 正規会員 | |
| 0002 | 山田 | 太郎 | 1981/3/1 | M | 2 | 2 | 退会 | |
| 0003 | 佐藤 | 長次郎 | 1980/4/2 | M | 1 | 1 | 正規会員 | |
| 0004 | 鈴木 | 定子 | 1980/8/2 | F | 1 | 1 | 正規会員 | |
| 0005 | 鈴本 | 花子 | 1980/12/14 | F | 1 | 1 | 正規会員 |
外部結合(LEFT, RIGHT, FULL OUTER JOIN)
結合可能なものは結合して、結合対象がないものは結合先をNULLとして抽出する。
LEFT OUTER JOINは左のテーブル全件に対して、右のテーブルで結合したレコードと結合相手がいない場合はNULLを表示する。(右テーブルのレコードで左テーブルと結合できなものは抽出から除外される)
RIGHTはLEFTの逆。
FULL OUTER JOINは両テーブルの全件を対象に結合できるレコードは結合して、結合できない場合は相手テーブルのデータがNULL(データなし)になる。
*OUTERを省略することも可能
SELECT MEMBER_LIST_1ST.ID, MEMBER_LIST_1ST.STATUS_ID, MEMBER_LIST_2ND.ID, MEMBER_LIST_2ND.STATUS_ID FROM MEMBER_LIST_1ST
FULL OUTER JOIN MEMBER_LIST_2ND
ON MEMBER_LIST_1ST.ID = MEMBER_LIST_2ND.ID
2つの会員名簿をIDで結合して、相手方がいない場合は相手方のデータはnullになる。
ここではID"0005"は「2年目会員名簿」にしかないので、これに対応する「1年目会員名簿」の値はnullになる
| MEMBER_LIST_1ST.ID | MEMBER_LIST_1ST.STATUS_ID | MEMBER_LIST_2ND.ID | MEMBER_LIST_2ND.STATUS_ID |
|---|---|---|---|
| 0001 | 1 | 0001 | 1 |
| 0002 | 1 | 0002 | 2 |
| 0003 | 1 | 0003 | 1 |
| 0004 | 1 | 0004 | 1 |
| null | null | 0005 | 1 |
クロス結合(CROSS JOIN)
クロス結合は結合するテーブル同士ののすべてのレコード組合せパターンを出す。
実用性はあまりない。1000レコードを持つテーブル同士を結合させれば1,000*1,000で1,000,000レコード結果が得られるので、
絞り込みを組合せないと結果のデータが大量になってしまう。
クロス結合ではすべてのレコード同士を結合するのでキーを指定しない。
SELECT ID, STATUS_TEXT FROM MEMBER_LIST_1ST
CROSS JOIN STATUS_DIV
会員のIDとステータステキストの組合せがすべて取得できる
| ID | STATUS_TEXT |
|---|---|
| 0001 | 正規会員 |
| 0001 | 退会 |
| 0002 | 正規会員 |
| 0002 | 退会 |
| 0003 | 正規会員 |
| 0003 | 退会 |
| 0004 | 正規会員 |
| 0004 | 退会 |
集合
複数のSELECT句での抽出結果を一緒にする。
条件の指定では複雑化するときなどに利用するが、集合させる各抽出結果のフォーマットが同じ形式になっていることが必要になる。
*後程追記します