最近、SQLをあまり触れていないことに気づいたので、SQLの復習として下記についてまとめてみました。

選択・射影・集合演算
JOIN（内部結合・外部結合）
GROUP BY と HAVING
副問合せ・相関副問合せ

本記事では、上記の基本的な動作をサンプルテーブル付きで整理します。

基本情報技術者試験や、応用情報技術者試験等の資格試験でも使用する項目ですので、試験勉強としても活用いただけると大変嬉しいです。

▼問合せ

下記の<商品テーブル>に対して実行していきます。

商品コード	商品名	単価
1	鉛筆	100
2	消しゴム	120
3	ボールペン	160
4	コンパス	250
5	定規	180
6	三角定規	190

●選択

条件を指定してテーブルから行を取得する操作のこと
FROM <テーブル名>の後にWHERE句を用いて条件を記載します

SELECT * FROM 商品 WHERE 商品コード = 1

実行結果

商品コード	商品名	単価
1	鉛筆	100

選択では、記号演算子を使用することも可能です

SELECT * FROM 商品 WHERE 単価 >= 150;

実行結果

商品コード	商品名	単価
3	ボールペン	160
4	コンパス	250
5	定規	180
6	三角定規	190

●射影

テーブルから列を取得する操作のこと
SELECT の後に項目名を列挙します

SELECT 商品名, 単価 FROM 商品;

実行結果

商品名	単価
鉛筆	100
消しゴム	120
ボールペン	160
コンパス	250
定規	180
三角定規	190

●整列

取得結果を指定した項目順に並び変えます
ORDER BY の後に並び替えたい項目を記載し、降順はDESC、昇順はASCを記載します
ASC、DESCを記載しない場合は ASC が採用され、昇順に並び替えられます

SELECT * FROM 商品 WHERE 単価 >= 150 ORDER BY 単価 DESC;

実行結果

商品コード	商品名	単価
4	コンパス	250
6	三角定規	190
5	定規	180
3	ボールペン	160

COALESCEを用いると、 NULL のデータを置き換えることができます
下記の場合は単価が NULL のデータを0に変換しています

SELECT COALESCE(単価, 0) FROM 商品;

●直積

2つのデータのすべての組み合わせを得る操作です
FROM句にて、カンマ区切りでテーブルを記載します
CROSS JOIN を用いて取得することもできます

下記2つのテーブルを商品コードで結合していきます

商品コード	商品名
1	鉛筆
2	消しゴム

色コード	色名
R	赤
B	青

SELECT 商品名, 色名 FROM 商品 CROSS JOIN 色;
SELECT 商品名, 色名 FROM 商品, 色;

出力結果

商品名	色名
鉛筆	赤
鉛筆	青
消しゴム	赤
消しゴム	青

●和

2つのSELECT結果をまとめて1つにする操作です
重複行は除外されます（UNION）
重複も残す場合は UNION ALL を使用します

下記2つの（東京/大阪）テーブルを使用します

社員ID	名前
1	田中
2	鈴木
3	佐藤

社員ID	名前
3	佐藤
4	高橋
5	伊藤

重複がない場合（UNION）は下記となります

SELECT 社員ID, 名前 FROM 東京支店
UNION
SELECT 社員ID, 名前 FROM 大阪支店;

実行結果

社員ID	名前
1	田中
2	鈴木
3	佐藤
4	高橋
5	伊藤

重複も残す場合（UNION ALL）は下記となります

SELECT 社員ID, 名前 FROM 東京支店
UNION ALL
SELECT 社員ID, 名前 FROM 大阪支店;

実行結果

社員ID	名前
1	田中
2	鈴木
3	佐藤
3	佐藤
4	高橋
5	伊藤

●積

2つのSELECT結果に共通して含まれる行だけを取り出す操作です
両方に存在する行のみ取得します
列数・型は一致していなければいけません
SQLでは INTERSECT を用います

下記2つの（東京/大阪）テーブルを使用します

社員ID	名前
1	田中
2	鈴木
3	佐藤

社員ID	名前
3	佐藤
4	高橋
5	伊藤

SELECT 社員ID, 名前 FROM 東京支店
INTERSECT
SELECT 社員ID, 名前 FROM 大阪支店;

実行結果

社員ID	名前
3	佐藤

MySQLはINTERSECT に非対応ですので、注意してください

▼結合

下記の<商品テーブル><売上明細テーブル>に対して実行していきます。

商品コード	商品名	単価
1	鉛筆	100
2	消しゴム	120
3	ボールペン	160

売上番号	商品コード	数量
1	2	10
2	3	24

●内部結合

複数テーブル間で結合条件に一致する行だけを取得するSQLの結合方法です
INNER JOIN を用いて内部結合します
ON句に結合条件として、どの列同士を突き合わせて業を結合するかを記載します

SELECT 商品名, 数量 
FROM 商品 
INNER JOIN 売上明細 
  ON 商品.商品コード = 売上明細.商品コード;

実行結果

商品名	数量
消しゴム	10
ボールペン	24

●外部結合

結合条件に一致しない行も含めて取得するSQLの結合方法です
LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN の3種類が存在します

LEFT OUTER JOIN

左テーブルの全行を取得します
右テーブルに存在しない場合は NULL とします

SELECT 商品名, 数量
FROM 商品
LEFT OUTER JOIN 売上明細
  ON 商品.商品コード = 売上明細.商品コード;

実行結果

商品名	数量
鉛筆	NULL
消しゴム	10
ボールペン	24

RIGHT OUTER JOIN

右テーブルの全行を取得します
左テーブルに存在しない場合は NULL とします

SELECT 商品名, 数量
FROM 商品
RIGHT OUTER JOIN 売上明細
  ON 商品.商品コード = 売上明細.商品コード;

実行結果

商品名	数量
消しゴム	10
ボールペン	24

FULL OUTER JOIN

両方のテーブルの全行を取得します
一致しない場合は NULL とします

SELECT 商品名, 数量
FROM 商品
FULL OUTER JOIN 売上明細
  ON 商品.商品コード = 売上明細.商品コード;

実行結果

商品名	数量
鉛筆	NULL
消しゴム	10
ボールペン	24

JOINの利用について
複数テーブルの情報を1つの結果セットにまとめたい場合はJOINを使用することが多いです。
外部キーで結ばれたテーブル同士の参照は、基本的にはJOINが適切となります。

▼グループ化

特定の列ごとに行をまとめ、集計関数で統計を出す操作です
同じ値の行を1グループにまとめます
集計関数（SUM、COUNT、AVG、MAX、MIN）とセットで使用することができます
SELECT句にある通常列は GROUP BY に含める必要があります
グループ後の条件には HAVING を用います

下記テーブルに対して実行していきます。

売上ID	商品名	数量
1	鉛筆	2
2	鉛筆	3
3	消しゴム	1
4	消しゴム	4

上記テーブルから、商品ごとの合計数量を出力します。

SELECT 商品名, SUM(数量) AS 合計数量
FROM 売上
GROUP BY 商品名;

実行結果

商品名	合計数量
鉛筆	5
消しゴム	5

WHERE はGROUP BY にてグループ化を行う前に行を絞り込みし、HAVINGはグループ化後に集計結果で絞り込みを行うために用います

HAVINGを用いた例として、「合計数量が5以上の商品」だけを表示してみます。

SELECT 商品名, SUM(数量) AS 合計数量
FROM sales
GROUP BY 商品名
HAVING SUM(数量) >= 5;

実行結果

商品名	合計数量
鉛筆	5
消しゴム	5

▼副問合せ

SQLの途中で一時的に実行される「小さな検索」のことです
取得した結果を用いて、外側のSQLが材料として利用します
外側にSQLを記載後、WHERE の条件を括弧で囲み、括弧内に一時的に実行するSQLを記載します

下記テーブルに対して実行していきます。

売上ID	商品名	数量
1	鉛筆	2
2	鉛筆	3
3	消しゴム	1
4	消しゴム	4

上記テーブルより、「平均数量より多い売り上げ」を取得してみます。

SELECT *
FROM sales
WHERE 数量 > (
    SELECT AVG(数量)
    FROM sales
);

実行結果

売上ID	商品名	数量
2	鉛筆	3
4	消しゴム	4

どんなときに副問合せを使うのか？
集計結果や条件判定に使う一時的な検索は副問合せが向いています。
「平均より大きい」「最大値を持つ行」など...

相関副問合せ

外側のSQLと内側のSQLが「相関」して動くのが相関副問合せです
通常の副問合せは「一度だけ」実行されますが、相関副問合せは外側の1行1行を見ながら、その都度評価します
記載方法は副問合せと同様に記載します

下記テーブルに対して実行していきます。

売上ID	商品名	数量
1	鉛筆	2
2	鉛筆	3
3	消しゴム	1
4	消しゴム	4

上記テーブルより、「商品ごとの平均より多い売上」を相関副問合せにて取得してみます。

SELECT *
FROM sales s1
WHERE 数量 > (
    SELECT AVG(s2.数量)
    FROM sales s2
    WHERE s2.商品名 = s1.商品名
);

上記処理の意味としては、下記のとおりです

外側：売上を1行ずつ見る（s1）
内側：同じ商品名の平均数量を計算（s2）

実行結果

相関副問合せにて、鉛筆、消しゴムの平均は下記のように求まります。
鉛筆の平均 = (2 + 3)/2 = 2.5
消しゴムの平均 = (1 + 4)/2 = 2.5
その結果、「商品ごとの平均より多い売上」は下記のように取得されます。

売上ID	商品名	数量
2	鉛筆	3
4	消しゴム	4

相関副問合せには、EXISTSを用いるパターンも存在します。

SELECT *
FROM sales s
WHERE EXISTS (
    SELECT 1
    FROM sales x
    WHERE x.商品名 = s.商品名
      AND x.数量 >= 4
);

上記は、「同じ商品名で数量4以上の行が存在する商品だけ残す」を実行しています。

実行結果

売上ID	商品名	数量
3	消しゴム	1
4	消しゴム	4

■ まとめ

本記事では、SQLの基本操作として以下を整理しました。

WHEREによる抽出（選択）
列の取得（射影）
集合演算（UNION / INTERSECT）
JOIN（内部結合・外部結合）
GROUP BY / HAVING
副問合せ・相関副問合せ

SQLは「例を見ながら手を動かす」ことで一気に理解が進みます。
本記事が学習の整理に役立てば幸いです。