【SQL】SELECT文「SELECT * FROM テーブル名」から卒業したい

概要

「SELECT * FROM db_name.table_name;」からは卒業して、もう一歩踏み出したい方、一緒に頑張りましょう。

前提

当記事での用語

• 行(レコードと読み替えても良いです)
• 列(カラムと読み替えても良いです)
• データ(値と読み替えても良いです)
• テーブル(すみません表とは言わずテーブルにしました)

前提情報

• 今回はSQL実行環境としてOracle社が提供しているAPEXというWeb上でSQLを試せる環境で行う。
• 簡単なアカウント登録を行えば、サンプルデータを用いて様々なデータの取得を試せるのでオススメ。
• 当記事では、アカウント登録などの手順については割愛(調べればすぐに対応できるかと思います)。

※また用意されている「タスク・スプレッドシート」というサンプルデータを使用して試しています。(こちらも「APEX サンプルデータ」などと調べるとすぐに導入できます)
※後半は「EMP / DEPT」というサンプルデータを使用。

SELECT文とは？

• SQLにおいてデータを扱う言語をDML(Data Manipulation Language)と呼ぶ。
• 今回扱うSELECT文やINSERT、DELETE文などがDMLに該当する。
  (テーブル作成などで使用する言語はDDL(Data Definition Language）と呼ばれる)
• SELECT文はデータベースからデータを取り出すための構文。

SELECT文の基本的なキーワード

SELECT 列名 (どの列の)
FROM テーブル名(どのテーブルから)
WHERE 条件(どんな検索条件を指定するか)
;(1つのSQL文の終わりを示す)

-- 例： ユーザ一覧テーブルから(FROM) id列のデータが「101」に該当する(WHERE) 全列のデータを取得する(SELECT)
SELECT * FROM users WHERE id = 101;

SELECT文にはたくさんの構文やオプションが存在しています。
今回は、よく目にするものを中心に調査していきます。

(おさらい)全列のデータを取得

-- SELECT * FROM テーブル名;

-- 例：　アスタリスク(*)は全ての列を指定するという意味になる
SELECT * FROM EBA_TASKS_SS;

特定の列名を指定(列名を指定)

-- SELECT 列名 FROM テーブル名;

-- 例：　TASK_NAME列を指定
SELECT TASK_NAME FROM EBA_TASKS_SS;

条件を指定(WHERE)

• 条件値に数値以外の文字列や日付を指定する場合、シングルコーテーション(')で囲う必要がある。
• 比較演算子と呼ばれる条件を評価するための演算子を使用する。

-- SELECT 列名 FROM テーブル名 WHERE 列名 比較演算子 条件値;

-- 例：　ID列のデータが「293672572702921909368653220424653765306」の全データを取得
SELECT * FROM EBA_TASKS_SS WHERE ID = 293672572702921909368653220424653765306;

-- 例： START_DATE列のデータが「22-04-06」以上の全データを取得
SELECT * FROM EBA_TASKS_SS WHERE START_DATE >= '22-04-06';

複数条件を指定(WHERE句 + 論理演算子(AND / OR))

-- SELECT 列名 FROM テーブル名 WHERE 列名 比較演算子 条件値 論理演算子 比較演算子 列名 条件値;

-- 例：　END_DATE列のデータが「22-04-07」以上かつ、STATUS列のデータが「Closed」のTASK_NAME、END_DATE、STATUS列のデータを取得
SELECT TASK_NAME, END_DATE, STATUS FROM EBA_TASKS_SS WHERE END_DATE >= '22-04-07' AND STATUS = 'Closed';

-- OR句 と AND句を併用した場合、ANDが優先される。(この例だとCOSTが「200」または、COSTが「600」かつSTATUSが「Open」のデータが対象となる)
SELECT TASK_NAME, COST, STATUS FROM EBA_TASKS_SS WHERE COST = 200 OR COST = 600 AND STATUS = 'Open';

-- ( )で囲うことで評価を優先できる。
SELECT TASK_NAME, COST, STATUS FROM EBA_TASKS_SS WHERE (COST = 200 OR COST = 600) AND STATUS = 'Open';

いずれかの条件に一致するデータを取得(IN)

-- OR句を使用していた例を置き換える
SELECT TASK_NAME, COST, STATUS FROM EBA_TASKS_SS WHERE COST IN (200, 600) AND STATUS = 'Open';

-- NOTを前に付けるといずれにも一致しない行が取得できる
SELECT TASK_NAME, COST, STATUS FROM EBA_TASKS_SS WHERE COST NOT IN (200, 600) AND STATUS = 'Open';

条件に範囲指定をする(BETWEEN)

-- SELECT 列名 FROM テーブル名 WHERE句 列名 BETWEEN 条件値 AND 条件値;

-- 例：　COST列のデータが「200」から「300」のデータを取得
SELECT * FROM EBA_TASKS_SS WHERE COST BETWEEN 200 AND 300;

-- 例：　NOTを前に付けると範囲外を指定できる
SELECT * FROM EBA_TASKS_SS WHERE COST NOT BETWEEN 200 AND 300;

あいまい検索(LIKE)※部分一致

指定できるパターン文字

パターン文字	内容
%	0個以上の任意の文字列
_	任意の1文字
[ ]	指定した範囲のいずれか
[^]	指定した範囲外

-- SELECT 列名 FROM テーブル名 WHERE 列名 LIKE 条件(パターン文字);

-- ASSIGNED_TO列が「J」から始まるデータを取得
SELECT * FROM EBA_TASKS_SS WHERE ASSIGNED_TO LIKE 'J%';

-- NOTを前に付けるとASSIGNED_TO列が「J」以外から始まるデータを取得
SELECT * FROM EBA_TASKS_SS WHERE ASSIGNED_TO NOT LIKE 'J%';

-- もちろんある文字の前に指定も可能　
-- （例：ASSIGNED_TO列で、「任意の1文字 + a + 以降が0個以上の文字」を持つデータを取得）
SELECT * FROM EBA_TASKS_SS WHERE ASSIGNED_TO LIKE '_a%';

列に別名を付ける(AS)

-- SELECT 列名 AS 列別名 FROM テーブル名 (..option);

-- 前項の例で別名を試してみる(WHERE句内で付けた別名を使用するとエラーになる)
SELECT TASK_NAME AS タスク, COST　AS コスト, STATUS　AS 状態 FROM EBA_TASKS_SS WHERE (COST = 200 OR COST = 600) AND STATUS = 'Open';

データを並べ替えて表示する(ORDER BY)

ソート順を指定できるキーワード

キーワード	内容	備考
ASC	昇順(小 -> 大)	何も指定しなければASCが指定されたことになる
DESC	昇順(大 -> 小)

-- SELECT 列名 FROM テーブル名 ORDER BY ソート列 (ASC/DESC);

-- ID列を昇順で並び替えて取得
SELECT * FROM EBA_TASKS_SS ORDER BY ID;

-- ID列を昇順で並び替えて取得(明示的にASCを指定)
SELECT * FROM EBA_TASKS_SS ORDER BY ID ASC;

-- ID列を降順で並び替えて取得
SELECT * FROM EBA_TASKS_SS ORDER BY ID DESC;

-- ソート列を複数指定
-- START_DATE列を降順(開始日が遅く)、END_DATEを昇順(終了日が早い)で並び替えて取得
SELECT * FROM EBA_TASKS_SS ORDER BY START_DATE DESC, END_DATE ASC;

グループ関数との組み合わせ

よく使いそうなグループ関数

グループ関数	内容	備考
MIN	対象列のデータのうち、最小値を返す
MAX	対象列のデータのうち、最大値を返す
SUM	対象列のデータの合計を返す
COUNT	対象列のデータの行数を返す	NULL値はカウントされない

-- SELECT 関数(列名) FROM テーブル名;

-- ID列の最小値を取得
SELECT MIN(ID) FROM EBA_TASKS_SS;

-- ID列の最大値を取得
SELECT MAX(ID) FROM EBA_TASKS_SS;

-- COST列の合計値を取得
SELECT SUM(COST) FROM EBA_TASKS_SS;

-- COST列が「200」という条件を指定し、行数を取得
SELECT COUNT(COST) FROM EBA_TASKS_SS WHERE COST = 200;

ここからややこしいですよね編

ここからAPEXで用意されている「EMP / DEPT」というサンプルデータで試していく。

結合

• 関係性のある複数のテーブルからデータを取得する方法として結合が存在する。
• 結合を行うために「JOINキーワード」が存在する(今回はその中のINNER JOIN)
• 関係性のあるテーブルは「共通列」を持ち、それらを正しく指定する必要がある。

テーブルを内部結合する(INNER JOIN)

• 一致するデータのみを検索(表示)する結合構文を「内部結合」という。
• 一致しないデータも含めて検索(表示)するのは「外部結合」という。
• テーブルの結合では、ONキーワードを使用し、明示的にテーブル同士の共通列を示すことが推奨される。
• ONキーワードを使用し、共通列を示す際は"テーブル名".列名のように表修飾を用いる("テーブル名"の部分を表修飾と呼ぶ)

-- SELECT 列名 FROM テーブル名a INNER JOIN テーブル名b ON テーブル名a.共通列 = テーブル名b.共通列(結合条件);

-- テーブルEMP、テーブルDEPTの共通列「DEPTNO」を結合条件として、EMP.ENAME、 EMP.JOB、 DEPT.DEPTNO列のデータを取得
SELECT EMP.ENAME, EMP.JOB, DEPT.DEPTNO FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

グループ化(GROUP BY)

-- SELECT グループ化対象列名, (グループ化対象を計算とか..) FROM テーブル名 GROUP BY グループ化対象列名;

-- JOB列をグループ化し、JOB一覧とCOUNT関数で取得したJOB別人数(別名列)のデータを取得
SELECT JOB, COUNT(JOB) AS JOB別人数 FROM EMP GROUP BY JOB;

グループ化した結果に対して条件を指定(HAVING)

• WHERE句での条件指定は1行ごとに対しての条件のため、グループ化した結果に対する条件はHAVING句を使用する。

-- SELECT グループ化対象列名, (グループ化対象を計算とか..) FROM テーブル名 GROUP BY グループ化対象列名 HAVING グループ化した結果に対する条件;

-- JOB列をグループ化し、JOB一覧とCOUNT関数で取得したJOB別人数が２以上のデータを取得
SELECT JOB, COUNT(JOB) AS JOB別人数 FROM EMP GROUP BY JOB HAVING COUNT(JOB) >= 2;