モーダルを閉じる工作HardwareHub ロゴ画像

工作HardwareHubは、ロボット工作や電子工作に関する情報やモノが行き交うコミュニティサイトです。さらに詳しく

利用規約プライバシーポリシー に同意したうえでログインしてください。

目次目次を開く/閉じる

理解が曖昧になりがちだけど運用上重要な SQL 文法をもう一度

モーダルを閉じる

ステッカーを選択してください

お支払い手続きへ
モーダルを閉じる

お支払い内容をご確認ください

購入商品
」ステッカーの表示権
メッセージ
料金
(税込)
決済方法
GooglePayマーク
決済プラットフォーム
確認事項

利用規約をご確認のうえお支払いください

※カード情報はGoogleアカウント内に保存されます。本サイトやStripeには保存されません

※記事の執筆者は購入者のユーザー名を知ることができます

※購入後のキャンセルはできません

作成日作成日
2015/06/11
最終更新最終更新
2021/10/07
記事区分記事区分
一般公開

運用時などに必要になるけれど、使用頻度が低く理解が曖昧になりがちな SQL をチートシート化しておきます。

重複を省いて SELECT (distinct)

SELECT 時

SELECT DISTINCT columnname FROM tablename

COUNT との併用

SELECT COUNT(DISTINCT columnname) FROM tablename

同じようなテーブルを結合してから SELECT (union)

サブクエリの結果を結合して、一時的なテーブルを作成する際に重宝します。

  • UNION → 重複レコードは取得しない
  • UNION ALL → 重複レコードも取得する

UNION ALL で同じようなテーブルを結合して columnname の個数を集計する例

SELECT columnname, COUNT(*) FROM
  (
   SELECT columnname FROM tablename1 UNION ALL
   SELECT columnname FROM tablename2
   ) AS cname
  GROUP BY columnname

テーブルを結合 (SELECT 結果を INSERT)

tablename2 と tablename3 を結合して、何らかの用途で必要になった tablename1 を作成する例

INSERT INTO tablename1 (SELECT columnname1, columnnam2 FROM tablename2)
INSERT INTO tablename1 (SELECT columnname1, columnnam2 FROM tablename3)

特定の GROUP のみを表示

レコードは WHERE で条件指定しますが、グループ (GROUP BY した結果) は HAVING で条件指定します。

SELECT columnname, COUNT(*) FROM tablename GROUP BY columnname HAVING COUNT(*) > 1

JOIN の使い分け

tablename1

a 1
b 1
c 2

tablename2

1 D
3 E

LEFT JOIN

tablename1 に tablename2 を左外部結合 (LEFT JOIN、LEFT OUTER JOIN)

a 1 D
b 1 D
c 2 NULL

RIGHT JOIN

tablename1 に tablename2 を右外部結合 (RIGHT JOIN、RIGHT OUTER JOIN)

a    1 D
b    1 D
NULL 3 E

FULL OUTER JOIN

tablename1 と tablename2 を完全外部結合 (FULL OUTER JOIN)

a    1 D
b    1 D
c    2 NULL
NULL 3 E

INNER JOIN

tablename1 と tablename2 を内部結合 (INNER JOIN)

a 1 D
b 1 D

単に JOIN と記載すると INNER JOIN になります。結合される二つのテーブルのどちらかにしか存在しないレコードは抽出されません。

CROSS JOIN

外積と内積に加えて直積 (CROSS JOIN) もありますが、実運用で使用することはあまりありません。直積の性質上、外積や内積の場合と異なり ON などで結合条件を指定しません。

SELECT * FROM tablename1 AS t1 CROSS JOIN tablename2 AS t2

結合結果のレコード数は「tablename1 のレコード数 * tablename2 のレコード数」となります。

a 1 1 D
a 1 3 E
b 1 1 D
b 1 3 E
c 2 1 D
c 2 3 E

補足

同じテーブル同士を結合することもできます。自己結合とよびます。テーブル名を区別できないため AS によるエイリアスが必要です。

SELECT alias1.id, alias2.id FROM tablename AS alias1 LEFT JOIN tablename AS alias2 ON alias1.id = alias2.id

ウィンドウ関数

サンプルデータ

CREATE TABLE tbl (c1 VARCHAR(255), c2 VARCHAR(255));
INSERT INTO tbl (c1, c2) VALUES ('a','a'),('b','b'),('c','c'),('a','aa'),('b','bb'),('c','cc'),('a','aaa'),('b','bbb'),('c','ccc');

OVER に何も指定しないとウィンドウは一つです。

SELECT *, COUNT(*) OVER() FROM tbl;
+------+------+-----------------+
| c1   | c2   | COUNT(*) OVER() |
+------+------+-----------------+
| a    | a    |               9 |
| b    | b    |               9 |
| c    | c    |               9 |
| a    | aa   |               9 |
| b    | bb   |               9 |
| c    | cc   |               9 |
| a    | aaa  |               9 |
| b    | bbb  |               9 |
| c    | ccc  |               9 |
+------+------+-----------------+

ウィンドウを分割するためには PARTITION BY を指定します。

SELECT *, COUNT(*) OVER(PARTITION BY c1) FROM tbl;
+------+------+--------------------------------+
| c1   | c2   | COUNT(*) OVER(PARTITION BY c1) |
+------+------+--------------------------------+
| a    | a    |                              3 |
| a    | aa   |                              3 |
| a    | aaa  |                              3 |
| b    | b    |                              3 |
| b    | bb   |                              3 |
| b    | bbb  |                              3 |
| c    | c    |                              3 |
| c    | cc   |                              3 |
| c    | ccc  |                              3 |
+------+------+--------------------------------+

ウィンドウ内の各行を ORDER BY でソートすると集約対象がウィンドウ全体ではなく現在行までになります。この集約対象をフレームとよびます。

SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2) FROM tbl;
+------+------+--------------------------------------------+
| c1   | c2   | COUNT(*) OVER(PARTITION BY c1 ORDER BY c2) |
+------+------+--------------------------------------------+
| a    | a    |                                          1 |
| a    | aa   |                                          2 |
| a    | aaa  |                                          3 |
| b    | b    |                                          1 |
| b    | bb   |                                          2 |
| b    | bbb  |                                          3 |
| c    | c    |                                          1 |
| c    | cc   |                                          2 |
| c    | ccc  |                                          3 |
+------+------+--------------------------------------------+

各ウィンドウにおけるフレームは、既定でウィンドウ内の最初の行から現在行までですが、これを変更することができます。PRECEDINGFOLLOWING の片方だけ指定する場合は BETWEEN を省略できます。また、特殊な指定方法として、ウィンドウ内の先頭行 UNBOUNDED PRECEDING、最終行 UNBOUNDED FOLLOWING、現在行 CURRENT ROW があります。

SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
  ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) FROM tbl;
SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
  ROWS 1 PRECEDING) FROM tbl;

+------+------+---------------------------------------------------------------------------------------+
| c1   | c2   | COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
  ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) |
+------+------+---------------------------------------------------------------------------------------+
| a    | a    |                                                                                     1 |
| a    | aa   |                                                                                     2 |
| a    | aaa  |                                                                                     2 |
| b    | b    |                                                                                     1 |
| b    | bb   |                                                                                     2 |
| b    | bbb  |                                                                                     2 |
| c    | c    |                                                                                     1 |
| c    | cc   |                                                                                     2 |
| c    | ccc  |                                                                                     2 |
+------+------+---------------------------------------------------------------------------------------+

代表的なウィンドウ関数

ROW_NUMBER()

ウィンドウ内における連番を付与できます。

SELECT *,
  ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2 DESC)
FROM tbl
ORDER BY c2;

+------+------+-----------------------------------------------------+
| c1   | c2   | ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2 DESC) |
+------+------+-----------------------------------------------------+
| a    | a    |                                                   3 |
| a    | aa   |                                                   2 |
| a    | aaa  |                                                   1 |
| b    | b    |                                                   3 |
| b    | bb   |                                                   2 |
| b    | bbb  |                                                   1 |
| c    | c    |                                                   3 |
| c    | cc   |                                                   2 |
| c    | ccc  |                                                   1 |
+------+------+-----------------------------------------------------+

RANK()、DENSE_RANK()

ウィンドウ内における ORDER BY でソートしたときの順位を付与できます。以下では PARTITION BY を指定していないためウィンドウは一つです。DENSE_RANK() は同順を詰めて順位付けします。

SELECT *,
  RANK() OVER(ORDER BY c1),
  DENSE_RANK() OVER(ORDER BY c1)
FROM tbl;

+------+------+--------------------------+--------------------------------+
| c1   | c2   | RANK() OVER(ORDER BY c1) | DENSE_RANK() OVER(ORDER BY c1) |
+------+------+--------------------------+--------------------------------+
| a    | a    |                        1 |                              1 |
| a    | aa   |                        1 |                              1 |
| a    | aaa  |                        1 |                              1 |
| b    | b    |                        4 |                              2 |
| b    | bb   |                        4 |                              2 |
| b    | bbb  |                        4 |                              2 |
| c    | c    |                        7 |                              3 |
| c    | cc   |                        7 |                              3 |
| c    | ccc  |                        7 |                              3 |
+------+------+--------------------------+--------------------------------+

OVER が複数ある場合は WINDOW で作成したエイリアスを利用するとクエリが綺麗になります。

SELECT *,
  RANK() OVER(w),
  DENSE_RANK() OVER(w)
FROM tbl
WINDOW
  w AS (ORDER BY c1);

RANK() の応用例として、各ウィンドウ内で一位の行を抜き出すことができます。

SELECT t.* FROM
  (SELECT *,
    RANK() OVER(w) AS rnk,
    DENSE_RANK() OVER(w) AS drnk
  FROM tbl
  WINDOW
    w AS (ORDER BY c1)) AS t
WHERE
  t.rnk = 1;

+------+------+-----+------+
| c1   | c2   | rnk | drnk |
+------+------+-----+------+
| a    | a    |   1 |    1 |
| a    | aa   |   1 |    1 |
| a    | aaa  |   1 |    1 |
+------+------+-----+------+

LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()

ウィンドウ内の現在行から前 LAG()LEAD() に1行ずれた行の値を取得できます。

SELECT *,
  LAG(c2, 1) OVER(w),
  LEAD(c2, 1) OVER(w)
FROM tbl
WINDOW
  w AS (PARTITION BY c1 ORDER BY c2);

+------+------+--------------------+---------------------+
| c1   | c2   | LAG(c2, 1) OVER(w) | LEAD(c2, 1) OVER(w) |
+------+------+--------------------+---------------------+
| a    | a    | NULL               | aa                  |
| a    | aa   | a                  | aaa                 |
| a    | aaa  | aa                 | NULL                |
| b    | b    | NULL               | bb                  |
| b    | bb   | b                  | bbb                 |
| b    | bbb  | bb                 | NULL                |
| c    | c    | NULL               | cc                  |
| c    | cc   | c                  | ccc                 |
| c    | ccc  | cc                 | NULL                |
+------+------+--------------------+---------------------+

ウィンドウ内の最初の行、最後の行、ある指定した行の値を取得できます。

SELECT *,
  FIRST_VALUE(c2) OVER(w),
  LAST_VALUE(c2) OVER(w),
  NTH_VALUE(c2, 2) OVER(w)
FROM tbl
WINDOW
  w AS (PARTITION BY c1 ORDER BY c2);

+------+------+-------------------------+------------------------+--------------------------+
| c1   | c2   | FIRST_VALUE(c2) OVER(w) | LAST_VALUE(c2) OVER(w) | NTH_VALUE(c2, 2) OVER(w) |
+------+------+-------------------------+------------------------+--------------------------+
| a    | a    | a                       | aaa                    | aa                       |
| a    | aa   | a                       | aaa                    | aa                       |
| a    | aaa  | a                       | aaa                    | aa                       |
| b    | b    | b                       | bbb                    | bb                       |
| b    | bb   | b                       | bbb                    | bb                       |
| b    | bbb  | b                       | bbb                    | bb                       |
| c    | c    | c                       | ccc                    | cc                       |
| c    | cc   | c                       | ccc                    | cc                       |
| c    | ccc  | c                       | ccc                    | cc                       |
+------+------+-------------------------+------------------------+--------------------------+
Likeボタン(off)0
詳細設定を開く/閉じる
アカウント プロフィール画像

業務ではデータベース設計とSQL最適化を担当

記事の執筆者にステッカーを贈る

有益な情報に対するお礼として、またはコメント欄における質問への返答に対するお礼として、 記事の読者は、執筆者に有料のステッカーを贈ることができます。

>>さらに詳しくステッカーを贈る
ステッカーを贈る コンセプト画像

Feedbacks

Feedbacks コンセプト画像

    ログインするとコメントを投稿できます。

    ログインする

    関連記事

    • MySQL レプリケーション設定 (2段, 3段)
      MySQL DB サーバは自身へのクエリをバイナリログとして書き出すことができます。レプリケーションとは、追加の MySQL DB サーバが、別の MySQL DB サーバが出力したバイナリログを自分のリレーログとよばれるログにコピーして、更にリレーログに記載されたクエリを自分自身のテーブルに実行する機能です。バイナリログを出力する DB をマスターとよび、自分のリレーログにコピーする DB をス...
      しおまめしおまめ10/7/2021に更新
      いいねアイコン画像0
    • MySQL 5.7 インストール手順
      2016/11/07 時点、多くの環境では yum レポジトリ等に mysql 5.7 が含まれていません。公式ページからダウンロードしてインストールする手順を二つまとめます。 yum レポジトリを追加する手順 [Installing MySQL on Linux Using the MySQL Yum Repository (mysql 5.7)](http://dev.mysql.com/do...
      しおまめしおまめ1/12/2021に更新
      いいねアイコン画像0
    • MyBatis 3 サンプルコード (Java/MySQL/Gradle)
      MyBatis は、JDBC を直接利用せずに、XML ファイル等で用意した SQL 文を利用して DB にアクセスするための Java ライブラリです。内部的には JDBC が利用されているため、JDBC のインストールは必要です。同様のライブラリに Hibernate ORM があります。DB は特に MySQL を対象として、MyBatis バージョン 3 の
      HARUTOHARUTO8/19/2019に更新
      いいねアイコン画像0
    • MySQL HandlerSocket Plugin の簡単な使用方法
      HandlerSocket は MySQL プロセス内のスレッドとして動作する、MySQL の NoSQL フロントエンドです。独自のプロトコルを用いて TCP 通信を行います。SQL 構文の解析が不要、プロトコルがシンプルなどの理由によって、単純な DB アクセスが高速に実行可能になります。MariaDB であれば[標準プラグインとして付属](https://mariadb.co
      しおまめしおまめ10/7/2021に更新
      いいねアイコン画像0
    • Spring Boot から MyBatis を利用するための設定 (Gradle/MySQL)
      MyBatis を Spring Boot で利用するための基本的な設定およびサンプルコードをまとめます。サンプルコードにおいては、特に MySQL を対象とします。 MyBatis Spring-Boot-Starter チュートリアル
      suzusuzu10/4/2021に更新
      いいねアイコン画像0