Go to Qiita Advent Calendar Top
LoginSignup
7
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@y-gaitoinポスタス株式会社

【SQL入門】 SELECT文を論理的に理解しよう

Posted at

はじめに

SQLを学び始めたばかりの初心者にとって、SELECT文は最初に出会う重要な構文です。

しかし、SELECT文の構文だけを覚えても、どのように処理されるのかを理解していないと、複雑なクエリを書くことができません。

今回はSELECTについてより深く理解するために、PostgreSQLの第7章で説明される『選択リスト』、『テーブル式』、『並び替え』について、じっくり解説しようと思います。

この記事で学べること

  • 記述したSELECT文が、どのように解釈されてデータを取得するか
  • 各句（FROM、WHERE、GROUP BY等）の役割と使い方
  • 実際の2次元表を使った視覚的な理解

SELECT文の記述順序と処理順序の違いについて

SELECT文は、記述順序と処理順序が異なります。

記述順序は以下の通りです。

SELECT
↓
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
ORDER BY
↓
LIMIT

上記の記述順序で実行されたSELECTは下記の処理順序で実行されます。

詳しくはPostgreSQL文書 SQLコマンド SELECTを参照ください。


処理順序は次のようになります。

テーブル式
↓
選択リスト
↓
問合せの結合（今回は省略します）
↓
行の並び替え
↓
LIMITとOFFSET

はい、いきなりなんのこっちゃという内容で戸惑うかもしれませんが、それぞれしっかりと説明します。

テーブル式について

テーブル式はテーブルを計算するためのものです。
テーブル式にはFROM句が含まれており、その後ろにオプションとしてWHERE句、GROUP BY句、HAVING句を付けることができます。 
単純なテーブル式は、単にディスク上のいわゆる基本テーブルと呼ばれるテーブルを参照するだけです。
しかし複雑な式では、様々な方法で基本テーブルを修正したり、結合させて使用することができます。

テーブル式のオプションWHERE句、GROUP BY句、およびHAVING句は、FROM句で派生したテーブル上に対して次々に変換を実行するパイプラインを指定します。
これらの変換によって仮想テーブルが1つ生成されます。
そしてこの仮想テーブルの行が選択リストに渡され、問い合わせの出力行が計算されます。

上記は、PostgreSQL文書 第7章 問い合わせ 7.2. テーブル式の文章です。

つまり、『テーブル式』とは下記のように表すことができます。

FROM
↓
WHERE
↓
GROUP BY
↓
HAVING

では『選択リスト』とは何を表すのか。

選択リストについて

SELECTコマンド中のテーブル式は、
テーブルやビューの結合、行の抽出、グループ化などにより中間の仮想テーブルを作ります。 
このテーブルは最終的に選択リストによる処理に渡されます。
選択リストは、中間のテーブルのどの列を実際に出力するかを決めます。

上記は、PostgreSQL文書 第7章 問い合わせ 7.3. 選択リストの文章です。

つまり、『テーブル式』で取得した中間結果から、出力したい列を選択しましょうということです。

上記を踏まえると、このように解釈ができます。

FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT

次に『行の並び替え』を見てみましょう。

行の並び替えについて

ある問い合わせが1つの出力テーブルを生成した後（選択リストの処理が完了した後）、
並べ替えることができます。並べ替えが選ばれなかった場合、行は無規則な順序で返されます。
そのような場合、実際の順序は、スキャンや結合計画の種類や、ディスク上に格納されている順序に依存します。
しかし、当てにしてはいけません。
明示的に並べ替え手続きを選択した場合にのみ、特定の出力順序は保証されます。

上記は、PostgreSQL文書 第7章 問い合わせ 7.5. 行の並べ替え(ORDER BY)の文章です。

これは、選択リストで抽出した結果について、並び替えが行えるということです。

上記を踏まえると、次のように解釈ができます。

FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY

では、最後に『LIMITとOFFSET』を見てみましょう。

LIMITとOFFSETについて

LIMITおよびOFFSETを使うことで、問い合わせの実行で生成された行の一部だけを取り出すことができます。

上記は、PostgreSQL文書 第7章 問い合わせ 7.6. LIMITとOFFSETの文章です。

これは抽出結果を一部分だけ取得ができるということです。

そのため、処理順序はこのように解釈ができます。

FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
↓
LIMIT OFFSET

データベースにおけるSELECT文はこのように解析されて実行されているということです。

そのため、GROUP BYの段階では、SELECTはまだ解釈されてないので、SELECT句で設定した列ラベルが使えなかったりするのです。

上記を念頭に置いたうえで、SELECT文についていくつかの例を示していきましょう。

サンプルデータの準備

今回は、競馬で活躍した史実の馬のデータを用いて説明します。

競馬ファンには馴染みのあるデータでSQLを学べるはずです。
なお、データはこちらを参照して作成しております。

競走馬テーブル（horses）

id name owner_id prize_money age
1 ディープインパクト 1 1,454,550,000 5
2 オルフェーヴル 1 1,344,080,000 6
3 ジェンティルドンナ 1 1,326,210,000 5
4 ゴールドシップ 2 1,397,760,000 6
5 キタサンブラック 3 1,876,840,000 6
6 ドゥラメンテ 1 516,600,000 5
7 リスグラシュー 4 887,380,000 6

馬主テーブル（owners）

id name
1 サンデーレーシング
2 小林英一
3 北島三郎
4 キャロットファーム

各句の詳細解説

１：FROM句 - テーブルの指定と結合

FROM句は、どのテーブルからデータを取得するかを指定します。

SELECT文の処理は、ここから始まります。

基本的な使い方

SELECT
    *
FROM
    horses
;

この場合、horsesテーブル全体が処理対象となります。

JOINによるテーブルの結合

複数のテーブルを結合する場合も、FROM句で指定します。結合には主に以下の種類があります。

INNER JOIN（内部結合）

両方のテーブルに存在するデータのみを取得します。

SELECT
    horses.name
,   owners.name AS owner_name
FROM
    horses
    INNER JOIN owners
    ON (
        horses.owner_id = owners.id
    )
;

処理結果：

name owner_name
ディープインパクト サンデーレーシング
オルフェーヴル サンデーレーシング
ジェンティルドンナ サンデーレーシング
ゴールドシップ 小林英一
キタサンブラック 北島三郎
ドゥラメンテ サンデーレーシング
リスグラシュー キャロットファーム
LEFT JOIN（左外部結合）

左側のテーブルのデータをすべて取得し、右側のテーブルに該当するデータがあれば結合します。

SELECT
    horses.name
,   owners.name AS owner_name
FROM
    horses
    LEFT JOIN owners
    ON (
        horses.owner_id = owners.id
    )
;

右側のテーブルに該当がない場合は、NULLが返されます。

例えば、馬主テーブル（owners）のレコードが下記のようになった場合、取得結果は次のようになります。

キャロットファームを削除した馬主テーブル（owners）

id name
1 サンデーレーシング
2 小林英一
3 北島三郎

処理結果：

name owner_name
ディープインパクト サンデーレーシング
オルフェーヴル サンデーレーシング
ジェンティルドンナ サンデーレーシング
ゴールドシップ 小林英一
キタサンブラック 北島三郎
ドゥラメンテ サンデーレーシング
リスグラシュー NULL

その他のJOIN

RIGHT JOIN（右外部結合）: 右側のテーブルのデータをすべて取得し、左側のテーブルに該当するデータがあれば結合します。
FULL OUTER JOIN（完全外部結合）: 両方のテーブルのデータをすべて取得します（PostgreSQL等で使用可能）。

※これらのJOINは実務で使用頻度が低いため、詳細は割愛します。LEFT JOINでほとんどの要件をカバーできます。

２：WHERE句 - フィルタリング

WHERE句は、FROM句で取得したデータから、条件に合う行だけを抽出します。

基本的な使い方

SELECT
    *
FROM
    horses
WHERE
    age >= 6
;

処理の流れ：

  1. FROM句でhorsesテーブル全体を取得
  2. WHERE句でage >= 6の条件に合う行だけを抽出

処理結果：

id name owner_id prize_money age
2 オルフェーヴル 1 1,344,080,000 6
4 ゴールドシップ 2 1,397,760,000 6
5 キタサンブラック 3 1,876,840,000 6
7 リスグラシュー 4 887,380,000 6

複数の条件を指定する

SELECT
    *
FROM
    horses
WHERE
    age >= 6
    AND owner_id = 2
;

処理結果：

id name owner_id prize_money age
4 ゴールドシップ 2 1,397,760,000 6

重要なポイント

  • WHERE句はGROUP BY句の前に処理されるため、集約関数（COUNT、SUM等）は使用できません
  • 集約後の結果をフィルタリングしたい場合は、後述するHAVING句を使用します

３：GROUP BY句 - 集約条件の指定

GROUP BY句は、指定したカラムでデータをグループ化し、各グループごとに集計を行います。

基本的な使い方

SELECT
    owner_id
,   COUNT(*) AS horse_count
FROM
    horses
GROUP BY
    owner_id
;

処理の流れ：

  1. FROM句でhorsesテーブル全体を取得
  2. GROUP BY句でowner_idごとにグループ化
  3. 各グループの行数をカウント

処理結果：

owner_id horse_count
1 4
2 1
3 1
4 1

視覚的な理解

GROUP BY句の処理を視覚的に表すと：

元のテーブル：

id name owner_id prize_money
1 ディープインパクト 1 1,454,550,000
2 オルフェーヴル 1 1,344,080,000
3 ジェンティルドンナ 1 1,326,210,000
4 ゴールドシップ 2 1,397,760,000
5 キタサンブラック 3 1,876,840,000
6 ドゥラメンテ 1 516,600,000
7 リスグラシュー 4 887,380,000

GROUP BY owner_id 後のグループ化：

  • グループ1（owner_id = 1）: ディープインパクト、オルフェーヴル、ジェンティルドンナ、ドゥラメンテ
  • グループ2（owner_id = 2）: ゴールドシップ
  • グループ3（owner_id = 3）: キタサンブラック
  • グループ4（owner_id = 4）: リスグラシュー

集約関数の使用

GROUP BY句と一緒によく使われる集約関数：

  • COUNT(*): 行数をカウント
  • SUM(カラム名): 合計値を計算
  • AVG(カラム名): 平均値を計算
  • MAX(カラム名): 最大値を取得
  • MIN(カラム名): 最小値を取得

例：馬主ごとの平均獲得賞金を計算

SELECT
    owner_id
,   AVG(prize_money) AS average_prize_money
FROM
    horses
GROUP BY
    owner_id
;

処理結果：

owner_id average_prize_money
1 1,160,360,000
2 1,397,760,000
3 1,876,840,000
4 887,380,000

重要なポイント

  • GROUP BY句で指定したカラムと、集約関数の結果のみをSELECT句で選択できます
  • GROUP BY句で指定していないカラムをSELECT句で選択するとエラーになります
  • GROUP BY句で指定したカラムはグループ化する際に、暗黙的にソートが発生します。しかし、これはソート順を保障するものではありません

４：HAVING句 - 集約結果のフィルタリング

HAVING句は、GROUP BY句で集約した結果に対してフィルタリングを行います。

WHERE句との違い

  • WHERE句: GROUP BY句の前に処理され、元のデータをフィルタリング
  • HAVING句: GROUP BY句の後に処理され、集約結果をフィルタリング

基本的な使い方

SELECT
    owner_id
,   COUNT(*) AS horse_count
FROM
    horses
GROUP BY
    owner_id
HAVING
    COUNT(*) >= 3
;

処理の流れ：

  1. FROM句でhorsesテーブル全体を取得
  2. GROUP BY句でowner_idごとにグループ化
  3. 各グループの行数をカウント
  4. HAVING句でCOUNT(*) >= 3の条件に合うグループだけを抽出

処理結果：

owner_id horse_count
1 4

馬主IDが2,3,4のグループは、競走馬数が1頭なので除外されます。

視覚的な理解

GROUP BY後の結果：

owner_id horse_count
1 4
2 1
3 1
4 1

HAVING COUNT(*) >= 3 適用後：

owner_id horse_count
1 4

実践例：平均獲得賞金が13億円以上の馬主を抽出

SELECT
    owner_id
,   AVG(prize_money) AS average_prize_money
FROM
    horses
GROUP BY
    owner_id
HAVING
    AVG(prize_money) >= 1300000000
;

処理結果：

owner_id average_prize_money
2 1,397,760,000
3 1,876,840,000

５：SELECT句 - カラムの指定

SELECT句は、最終的に取得するカラムを指定します。処理順序としては、GROUP BY句とHAVING句の後に実行されます。

基本的な使い方

SELECT
    name
,   prize_money
FROM
    horses
;

処理結果：

name prize_money
ディープインパクト 1,454,550,000
オルフェーヴル 1,344,080,000
ジェンティルドンナ 1,326,210,000
ゴールドシップ 1,397,760,000
キタサンブラック 1,876,840,000
ドゥラメンテ 516,600,000
リスグラシュー 887,380,000

DISTINCTによる重複の除去

DISTINCTを使用すると、重複する行を1つにまとめます。

例：馬主IDの一覧を取得

SELECT DISTINCT
    owner_id
FROM
    horses
;

処理の流れ：

  1. FROM句でhorsesテーブル全体を取得
  2. SELECT句でowner_idを選択
  3. DISTINCTで重複を除去

処理結果：

owner_id
1
2
3
4

DISTINCTなしの場合：

owner_id
1
1
1
2
3
1
4

DISTINCTありの場合：

owner_id
1
2
3
4

カラムに別名を付ける

ASキーワードを使用して、カラムに別名を付けることができます。

SELECT
    name AS horse_name
,   prize_money AS total_prize_money
FROM
    horses
;

処理結果：

horse_name total_prize_money
ディープインパクト 1,454,550,000
オルフェーヴル 1,344,080,000
ジェンティルドンナ 1,326,210,000
ゴールドシップ 1,397,760,000
キタサンブラック 1,876,840,000
ドゥラメンテ 516,600,000
リスグラシュー 887,380,000

６：ORDER BY句 - ソート条件の指定

ORDER BY句は、結果を指定したカラムで並び替えます。

基本的な使い方

SELECT
    name
,   prize_money
FROM
    horses
ORDER BY
    prize_money
;

処理結果（昇順）：

name prize_money
ドゥラメンテ 516,600,000
リスグラシュー 887,380,000
ジェンティルドンナ 1,326,210,000
オルフェーヴル 1,344,080,000
ゴールドシップ 1,397,760,000
ディープインパクト 1,454,550,000
キタサンブラック 1,876,840,000

降順でソート

DESCキーワードを使用すると、降順（大きい順）でソートできます。

SELECT
    name
,   prize_money
FROM
    horses
ORDER BY
    prize_money DESC
;

処理結果（降順）：

name prize_money
キタサンブラック 1,876,840,000
ディープインパクト 1,454,550,000
ゴールドシップ 1,397,760,000
オルフェーヴル 1,344,080,000
ジェンティルドンナ 1,326,210,000
リスグラシュー 887,380,000
ドゥラメンテ 516,600,000

複数のカラムでソート

複数のカラムを指定すると、最初のカラムでソートし、同じ値の場合は次のカラムでソートします。

SELECT
    owner_id
,   name
,   prize_money
FROM
    horses
ORDER BY
    owner_id
,   prize_money DESC
;

処理結果：

owner_id name prize_money
1 ディープインパクト 1,454,550,000
1 オルフェーヴル 1,344,080,000
1 ジェンティルドンナ 1,326,210,000
1 ドゥラメンテ 516,600,000
2 ゴールドシップ 1,397,760,000
3 キタサンブラック 1,876,840,000
4 リスグラシュー 887,380,000

７：LIMIT句 - 取得件数の指定

LIMIT句は、取得する行数を制限します。大量のデータから必要な分だけ取得する際に使用します。

基本的な使い方

SELECT
    name
,   prize_money
FROM
    horses
ORDER BY
    prize_money DESC
LIMIT
    3
;

処理の流れ：

  1. FROM句でhorsesテーブル全体を取得
  2. ORDER BY句で獲得賞金の降順にソート
  3. LIMIT句で上位3件のみを取得

処理結果：

name prize_money
キタサンブラック 1,876,840,000
ディープインパクト 1,454,550,000
ゴールドシップ 1,397,760,000

ページネーション

OFFSETと組み合わせることで、ページネーションを実現できます。

-- 2ページ目を取得（1ページあたり3件、OFFSETは3）
SELECT
    name
,   prize_money
FROM
    horses
ORDER BY
    prize_money DESC
LIMIT
    3
OFFSET
    3
;

処理結果：

name prize_money
オルフェーヴル 1,344,080,000
ジェンティルドンナ 1,326,210,000
リスグラシュー 887,380,000

実践的なSQL例文

ここまで学んだ内容を組み合わせて、実践的なSQL例文を見ていきましょう。

例1：馬主ごとの平均獲得賞金を高い順に表示（平均獲得賞金13億円以上のみ）

SELECT
    d.name AS owner_name
,   AVG(e.prize_money) AS average_prize_money
FROM
    horses e
    INNER JOIN owners d
    ON (
        e.owner_id = d.id
    )
GROUP BY
    d.name
HAVING
    AVG(e.prize_money) >= 1300000000
ORDER BY
    average_prize_money DESC
;

処理の流れ：

  1. FROM句: horsesとownersをINNER JOINで結合
  2. WHERE句: なし（全データが対象）
  3. GROUP BY句: 馬主名でグループ化
  4. HAVING句: 平均獲得賞金が7億円以上のグループのみ抽出
  5. SELECT句: 馬主名と平均獲得賞金を選択
  6. ORDER BY句: 平均獲得賞金の降順でソート
  7. LIMIT句: なし（全件取得）

処理結果：

owner_name average_prize_money
北島三郎 1,876,840,000
小林英一 1,397,760,000

例2：獲得賞金が高い順に上位5名を表示

SELECT
    e.name AS horse_name
,   d.name AS owner_name
,   e.prize_money
FROM
    horses e
    LEFT JOIN owners d
    ON (
        e.owner_id = d.id
    )
ORDER BY
    e.prize_money DESC
LIMIT
    5
;

処理結果：

horse_name owner_name prize_money
キタサンブラック 北島三郎 1,876,840,000
ディープインパクト サンデーレーシング 1,454,550,000
ゴールドシップ 小林英一 1,397,760,000
オルフェーヴル サンデーレーシング 1,344,080,000
ジェンティルドンナ サンデーレーシング 1,326,210,000

例3：馬主ごとの競走馬数と平均年齢を表示（競走馬数2名以上）

SELECT
    d.name AS owner_name
,   COUNT(*) AS horse_count
,   AVG(e.age) AS average_age
FROM
    horses e
    INNER JOIN owners d
    ON (
        e.owner_id = d.id
    )
GROUP BY
    d.name
HAVING
    COUNT(*) >= 2
ORDER BY
      horse_count DESC
    , average_age DESC
;

処理結果：

owner_name horse_count average_age
サンデーレーシング 4 5.25

処理順序の再確認

SELECT文の処理順序を再度確認しましょう。

FROM → WHERE → GROUP BY → HAVING → SELECT（DISTINCT含む）→ ORDER BY → LIMIT

各段階でのデータの変化

  1. FROM句: テーブル全体を取得
  2. WHERE句: 条件に合う行だけを抽出（行数が減る）
  3. GROUP BY句: グループ化（行数が減る、集約される）
  4. HAVING句: 集約結果をフィルタリング（グループ数が減る）
  5. SELECT句: 必要なカラムだけを選択（カラム数が減る、DISTINCTで重複除去）
  6. ORDER BY句: 並び替え（行数は変わらない）
  7. LIMIT句: 取得件数を制限（行数が減る）

よくあるエラーとその原因

エラー1：WHERE句で集約関数を使用

-- エラーになる例
SELECT
    owner_id
,   COUNT(*)
FROM
    horses
WHERE
    COUNT(*) > 2  -- エラー！
;

原因: WHERE句はGROUP BY句の前に処理されるため、集約関数は使用できません。

正しい書き方:

SELECT
    owner_id
,   COUNT(*)
FROM
    horses
GROUP BY
    owner_id
HAVING
    COUNT(*) > 2
;

エラー2：GROUP BY句で指定していないカラムをSELECT

-- エラーになる例
SELECT
    name
,   owner_id
,   COUNT(*)
FROM
    horses
GROUP BY
    owner_id  -- nameがGROUP BYに含まれていない！
;

原因: GROUP BY句でグループ化した場合、SELECT句ではグループ化のキーと集約関数の結果のみを選択できます。

正しい書き方:

SELECT
    owner_id
,   COUNT(*)
FROM
    horses
GROUP BY
    owner_id
;

まとめ

この記事では、SELECT文の記述順序と処理順序を理解することで、SQLの基本を学びました。

重要なポイント

  1. 処理順序を理解する: 書く順番と処理される順番が異なる
  2. 各句の役割を理解する: FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY、LIMITそれぞれの役割
  3. WHEREとHAVINGの違い: WHEREは集約前、HAVINGは集約後のフィルタリング

参考資料

7
2
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
Sign upLogin
7
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?