はじめに
Qiitaには、山ほどSQLの解説記事がありますが、私も記載してみました。
記載した理由としては、最近Oracle Master Silverの再取得(バージョンが11gから19cなので再取得と言わないかもだけど)をしたのですが、SQLの箇所が試験結果として弱かったので、SQLに関する知識を整理し、特に弱点だった部分の復習を兼ねて記事を執筆しました。
私は、インフラよりのエンジニアなので基盤系の箇所の設計や運用の経験あったのですが、アプリ要素の多いSQLの箇所は非常に弱いんですね。(言い訳)
記載対象SQL
対象は、あまりインフラエンジニア側で扱う機会が少なさそうな以下のSQLについてです。
| No. | 機能 | 説明 | SQLの例 | 利用条件・制限 |
|---|---|---|---|---|
| 1 | 内部結合 | 両方のテーブルに共通するデータのみを返します。 | sql SELECT 顧客.名前, 注文.注文日 FROM 顧客 INNER JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID; | 両方のテーブルに共通するカラムが必要。 |
| 2 | 左外部結合 | 左テーブル(join句の左側という意味)の全ての行を取得し、右テーブルの一致するデータを結合します。 | sql SELECT 顧客.名前, 注文.注文日 FROM 顧客 LEFT JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID; | 左テーブルに対応する右テーブルの行がない場合、右列はNULL。 |
| 3 | 右外部結合 | 右テーブルの全ての行を取得し、左テーブルの一致するデータを結合します。 | sql SELECT 顧客.名前, 注文.注文日 FROM 顧客 RIGHT JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID; | 右テーブルに対応する左テーブルの行がない場合、左列はNULL。 |
| 4 | 完全外部結合 | 両方のテーブルの全ての行を返し、共通するデータも含まれます。 | sql SELECT 顧客.名前, 注文.注文日 FROM 顧客 FULL OUTER JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID; | 使えるデータベースシステムがあったりなかったりする。 |
| 5 | 自然結合 | 両方のテーブルで共通するすべての列をもとに結合します。 | sql SELECT * FROM 顧客 NATURAL JOIN 注文; | 両方のテーブルに共通するカラムが必要。 |
| 6 | 非等価結合 | 結合条件に等価演算子以外の演算子を使用します。 | sql SELECT 社員.名前, 給与.金額 FROM 社員 JOIN 給与 ON 社員.年齢 BETWEEN 給与.年齢_MIN AND 給与.年齢_MAX; | 結合条件に等価演算子以外の演算子が必要。 |
| 7 | INTERSECT | 二つのSELECT文の結果の共通部分を返します。 | sql SELECT 名前 FROM 顧客A INTERSECT SELECT 名前 FROM 顧客B; | 両方のSELECT文のカラム数とデータ型が一致している必要。 |
| 8 | UNION | 二つのSELECT文の結果を結合し、重複を取り除きます。 | sql SELECT 名前 FROM 顧客A UNION SELECT 名前 FROM 顧客B; | 両方のSELECT文のカラム数とデータ型が一致している必要。 |
| 9 | UNION ALL | 二つのSELECT文の結果を結合し、重複を取り除かずに全ての行を返します。 | sql SELECT 名前 FROM 顧客A UNION ALL SELECT 名前 FROM 顧客B; | 両方のSELECT文のカラム数とデータ型が一致している必要。 |
前提となるテーブルの作成SQL
まず、以下のSQLを使用して、説明に使用する「顧客」テーブルと「注文」テーブルを作成し、データを挿入します。
-- 顧客テーブルの作成
CREATE TABLE 顧客 (
顧客ID INT PRIMARY KEY,
名前 VARCHAR(50),
年齢 INT
);
-- 注文テーブルの作成
CREATE TABLE 注文 (
注文ID INT PRIMARY KEY,
顧客ID INT,
注文日 DATE,
金額 INT,
FOREIGN KEY (顧客ID) REFERENCES 顧客(顧客ID)
);
-- 顧客テーブルにデータを挿入
INSERT INTO 顧客 (顧客ID, 名前, 年齢) VALUES (1, 'Alice', 30);
INSERT INTO 顧客 (顧客ID, 名前, 年齢) VALUES (2, 'Bob', 25);
INSERT INTO 顧客 (顧客ID, 名前, 年齢) VALUES (3, 'Charlie', 40);
-- 注文テーブルにデータを挿入
INSERT INTO 注文 (注文ID, 顧客ID, 注文日, 金額) VALUES (1, 1, '2024-08-01', 5000);
INSERT INTO 注文 (注文ID, 顧客ID, 注文日, 金額) VALUES (2, 2, '2024-08-03', 3000);
INSERT INTO 注文 (注文ID, 顧客ID, 注文日, 金額) VALUES (3, 1, '2024-08-05', 7000);
顧客テーブル
・SQL Developerの画面とその表
| 顧客ID | 名前 | 年齢 |
|---|---|---|
| 1 | Alice | 30 |
| 2 | Bob | 25 |
| 3 | Charlie | 40 |
注文テーブル
・SQL Developerの画面とその表
| 注文ID | 顧客ID | 注文日 | 金額 |
|---|---|---|---|
| 1 | 1 | 2024-08-01 | 5000 |
| 2 | 2 | 2024-08-03 | 3000 |
| 3 | 1 | 2024-08-05 | 7000 |
1. 内部結合（INNER JOIN）
SELECT 顧客.名前, 注文.注文日, 注文.金額
FROM 顧客
INNER JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID;
説明
このクエリは、顧客テーブルと注文テーブルを内部結合し、両方に共通するデータ（つまり、注文をした顧客の情報）を取得します。この場合、AliceとBobのデータが注文テーブルと一致します。
結果には、注文をした顧客のみが返されています。Charlieは注文をしていないため（注文テーブルにCharlieのデータがないため）、結果に含まれていません。
実行結果
・SQL Developerの画面とその表
| 名前 | 注文日 | 金額 |
|---|---|---|
| Alice | 2024-08-01 | 5000 |
| Bob | 2024-08-03 | 3000 |
| Alice | 2024-08-05 | 7000 |
2. 左外部結合（LEFT OUTER JOIN）
SELECT 顧客.名前, 注文.注文日, 注文.金額
FROM 顧客
LEFT JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID;
説明
左外部結合では、左側（JOINの左にある顧客テーブル）のすべての行を取得し、それに対応する注文データを結合します。注文がない顧客（この場合はCharlie）も結果に含まれますが、その注文情報はNULLになります。顧客IDが顧客テーブルに存在するユーザーはすべて結果に含まれます。
実行結果
・SQL Developerの画面とその表
| 名前 | 注文日 | 金額 |
|---|---|---|
| Alice | 2024-08-01 | 5000 |
| Bob | 2024-08-03 | 3000 |
| Alice | 2024-08-05 | 7000 |
| Charlie | NULL | NULL |
3. 右外部結合（RIGHT OUTER JOIN）
SELECT 顧客.名前, 注文.注文日, 注文.金額
FROM 顧客
RIGHT JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID;
説明
右外部結合は、右側の注文テーブルのすべての行を取得し、それに対応する顧客情報を結合します。全ての注文が結果に含まれ、対応する顧客情報がない場合はNULLになります。ただし、この例ではすべての注文が顧客と一致しているため、顧客名もすべて表示されます。結果には、注文に対応するすべての顧客情報が含まれています。
実行結果
・SQL Developerの画面とその表
| 名前 | 注文日 | 金額 |
|---|---|---|
| Alice | 2024-08-01 | 5000 |
| Bob | 2024-08-03 | 3000 |
| Alice | 2024-08-05 | 7000 |
4. 完全外部結合（FULL OUTER JOIN）
SELECT 顧客.名前, 注文.注文日, 注文.金額
FROM 顧客
FULL OUTER JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID;
説明
完全外部結合では、顧客テーブルと注文テーブルの両方からすべての行を取得し、共通するデータを結合します。これにより、注文がない顧客や、顧客が存在しない注文も結果に含まれます。結果には、注文を行っていないCharlieの情報も含まれます。
実行結果
・SQL Developerの画面とその表
| 名前 | 注文日 | 金額 |
|---|---|---|
| Alice | 2024-08-01 | 5000 |
| Bob | 2024-08-03 | 3000 |
| Alice | 2024-08-05 | 7000 |
| Charlie | NULL | NULL |
5. 自然結合（NATURAL JOIN）
SELECT *
FROM 顧客
NATURAL JOIN 注文;
説明
自然結合では、共通する列名に基づいて自動的に結合が行われます。この場合、顧客IDが共通列として使用されます。自然結合は、共通する列を自動的に基にして結合を行います。結果には、共通する顧客IDに基づいた顧客と注文のデータのみが含まれます。
実行結果
・SQL Developerの画面とその表
| 顧客ID | 名前 | 年齢 | 注文ID | 注文日 | 金額 |
|---|---|---|---|---|---|
| 1 | Alice | 30 | 1 | 2024-08-01 | 5000 |
| 2 | Bob | 25 | 2 | 2024-08-03 | 3000 |
| 1 | Alice | 30 | 3 | 2024-08-05 | 7000 |
6. 非等価結合（NON-EQUI JOIN）
非等価結合の例を実行する前に、もう一つのテーブル「給与」を作成し、データを挿入します。
・給与テーブルの作成とデータ挿入
CREATE TABLE 給与 (
年齢_MIN INT,
年齢_MAX INT,
金額 INT
);
INSERT INTO 給与 (年齢_MIN, 年齢_MAX, 金額) VALUES (20, 29, 200000);
INSERT INTO 給与 (年齢_MIN, 年齢_MAX, 金額) VALUES (30, 39, 300000);
INSERT INTO 給与 (年齢_MIN, 年齢_MAX, 金額) VALUES (40, 49, 400000);
給与テーブルの情報
| 年齢_MIN | 年齢_MAX | 金額 |
|---|---|---|
| 20 | 29 | 200000 |
| 30 | 39 | 300000 |
| 40 | 49 | 400000 |
非等価結合の実行例
SELECT 顧客.名前, 給与.金額
FROM 顧客
JOIN 給与 ON 顧客.年齢 BETWEEN 給与.年齢_MIN AND 給与.年齢_MAX;
説明
このクエリは、顧客の年齢が給与テーブルの年齢範囲に該当する場合に結合を行い、対応する給与を取得します。例えば、Aliceの年齢は30歳であるため、給与テーブルの30-39歳の範囲に該当し、対応する給与300,000円が取得されています。同様に、Bobは20-29歳の範囲で200,000円、Charlieは40-49歳の範囲で400,000円が取得されています。
実行結果
・SQL Developerの画面とその表
| 名前 | 金額 |
|---|---|
| Alice | 300000 |
| Bob | 200000 |
| Charlie | 400000 |
7. INTERSECT
SELECT 名前 FROM 顧客 WHERE 年齢 < 35
INTERSECT
SELECT 名前 FROM 顧客 WHERE 顧客ID IN (1, 2);
説明
このクエリは、年齢が35歳未満の顧客と、顧客IDが1または2の顧客の名前の共通部分を取得します。
結果には、年齢が35歳未満で、かつ顧客IDが1または2である顧客の名前が表示されます。AliceとBobがその条件に合致しているため、結果に含まれています。
実行結果
・SQL Developerの画面とその表
| 名前 |
|---|
| Alice |
| Bob |
8. UNION
SELECT 名前 FROM 顧客 WHERE 年齢 < 35
UNION
SELECT 名前 FROM 顧客 WHERE 顧客ID = 3;
説明
このクエリは、年齢が35歳未満の顧客と、顧客IDが3の顧客の名前を結合して取得します。重複するデータは取り除かれます。その結果、年齢が35歳未満のAliceとBob、および顧客IDが3のCharlieが含まれています。重複は除外されるため、各顧客の名前は結果に一度だけ表示されます。
実行結果
・SQL Developerの画面とその表
| 名前 |
|---|
| Alice |
| Bob |
| Charlie |
9. UNION ALL
SELECT 名前 FROM 顧客 WHERE 年齢 < 35
UNION ALL
SELECT 名前 FROM 顧客 WHERE 顧客ID = 3;
説明
このクエリは、年齢が35歳未満の顧客と、顧客IDが3の顧客の名前を結合して取得します。重複は除外されず、すべての行がそのまま表示されます。この結果では、UNION ALLを使用しているため、重複するデータもそのまま含まれます。そのため、AliceとBobが2回表示されており、重複が取り除かれていないことが確認できます。
実行結果
・SQL Developerの画面とその表
| 名前 |
|---|
| Alice |
| Bob |
| Charlie |
| Alice |
| Bob |