3 SQL言語 前編（解説）

この節では、問い合わせ(クエリ)を行うSELECT文を説明します。問い合わせとは、表からデータを検索する処理を指します。SELECT文は他のSQL文と比較して複雑なため、以下の順で説明します。

• 単純なSELECT文
• グループ化を含むSELECT文
• 複数の表を対象とするSELECT文

単純なSELECT文

SELECT文は表からデータを検索します。形式は以下の通りです。

SELECT [DISTINCT] {値式 [[AS] 別名] [, ...] | * }
    [FROM 表名 [[AS] 別名] [, ...]]
    [WHERE 条件]
    [GROUP BY 値式 [, ...]]
    [HAVING 条件]
    [{ UNION | INTERSECT | EXCEPT } [DISTINCT | ALL] SELECT文]
    [ORDER BY 値式 [ASC | DESC] [, ...] ]
    [LIMIT 行数]
    [OFFSET 行数]

• SELECT句は、出力する行を形成する列を指定します。
  値式は、出力する列名、値または値を計算する式です。表示目的で別の名前を付けることができます。
  値式の代わりにアスタリスク(*)を指定すると、表のすべての列を指定したものとみなされます。
  オプションのDISTINCTを指定すると、重複した行は出力から除去されます。省略時はALLで重複した行も出力されます。
• FROM句は、検索対象を指定します。
  表名は、検索する表の名前です。参照目的で別の名前を付けることができます。複数の表を指定する場合については、「複数の表を対象とするSELECT文」で説明します。FROM句を省略した場合は、列が何もない1行だけの仮想的な表が指定されたものとみなされます。
• WHERE句は、行の検索条件を指定します。
  条件は、行を選択する論理式です。この論理式が真とならない行はすべて出力から取り除かれます。論理式については別の節で説明します。WHERE句を省略した場合は、すべての行が出力の対象になります。
• ORDER BY句は、出力する行のソート方法を指定します。
  値式は、出力する列名または値を計算する式です。この値の順に行が並べ替えられます。オプションの ASC | DESC は昇順か降順かの指定で、省略時はASCで昇順になります。
• LIMIT句は、出力する行数を指定します。
  行数は、出力する最大の行数です。これを超える行は出力から除去されます。
• OFFSET句は、読み飛ばす行数を指定します。
  行数は、読み飛ばす最大の行数です。読み飛ばした残りの行が出力されます。

具体例として、SELECT句だけのSELECT文を以下に示します。電卓のような四則演算ができます。

SELECT 1+2*3;

次の例は、prod表の行数を出力するSELECT文です。COUNT関数については、別の節で説明します。

SELECT COUNT(*) FROM prod;

次の例は、prod表のすべてのデータを出力するSELECT文です。

SELECT * FROM prod;

次の例は、prod表からprice列が70未満の行のprod_name列とprice列を出力するSELECT文です。

SELECT prod_name, price
    FROM prod
    WHERE price < 70;

次の例は、DISTINCTを使用したSELECT文です。orders表のprod_id列の値を重複なしで出力します。

SELECT DISTINCT prod_id
    FROM orders;

次の例は、ORDER BY句、LIMIT句、OFFSET句を使用したSELECT文です。

SELECT order_id, prod_id, qty
    FROM orders
    ORDER BY qty DESC
    LIMIT 1
    OFFSET 2;

orders表をqty列の降順にソートし、最初の2行を読み飛ばすことにより、3番目に大きな値のみを出力しています。

グループ化を含むSELECT文

グループ化とは、指定された列の値が同じ行をまとめたグループに表を分割する操作です。例えば、以下のorders表について考えます。

orders表

order_id	order_date	customer_id	prod_id	qty
1	2024-04-01	1	1	10
2	2024-04-01	2	2	5
3	2024-04-01	3	3	8
4	2024-04-02	2	1	3
5	2024-04-02	3	2	4

orders表をprod_id列でグループ化すると、以下のように3グループに分割されます。

グループ化されたorders表

prod_id	order_id	order_date	customer_id	qty
1	1	2024-04-01	1	10
	4	2024-04-02	2	3
2	2	2024-04-01	2	5
	5	2024-04-02	3	4
3	3	2024-04-01	3	8

グループ化された表の検索は、グループ単位で行われます。例えば、行数のカウント結果は以下のようになります。

prod_id	count(*)
1	2
2	2
3	1

グループ化には、以下の句を使用します。

• GROUP BY句は、グループ化するためのキーを指定します。
  値式は、キーとなる列名または値を計算する式です。計算結果が同じ値になった行を1つのグループにまとめます。
• HAVING句は、グループの検索条件を指定します。
  条件は、グループを選択する論理式です。この論理式が真とならないグループはすべて出力から取り除かれます。論理式については別の節で説明します。HAVING句を省略した場合は、すべてのグループが出力の対象になります。

GROUP BY句を指定したSELECT文では、以下の点に注意する必要があります。

• SELECT句、HAVING句、ORDER BY句で指定できる値式は、ORDER BY句で指定したキーか集約関数に限定されます。理由は、グループ内で単一の値をとる必要があるためです。
• WHERE句はGROUP BY句よりも先に実行されるため、グループ化はWHERE句で選択された行に対して行われます。
• ORDER BY句、LIMIT句で指定する行数は、行ではなくグループの数になります。

具体例として、orders表をprod_id列でグループ化して、prod_id列と各グループの行数を出力するSELECT文を以下に示します。

SELECT prod_id, COUNT(*)
    FROM orders
    GROUP BY prod_id;

以下は、上記にHAVING句を追加して行数が2のグループのみを出力するSELECT文です。

SELECT prod_id, COUNT(*)
    FROM orders
    GROUP BY prod_id
    HAVING COUNT(*) = 2;

以下は、WHERE句とGROUP BY句を使ったSELECT文です。

SELECT prod_id, COUNT(*)
    FROM orders
    WHERE customer_id > 1
    GROUP BY prod_id;

orders表からcustomer_id列が 1 よりも大きな行を選択後に、prod_id列でグループ化しています。

複数の表を対象とするSELECT文

2つの表を対象とする検索には、集合演算と結合演算があります。

集合演算は、表を行を要素とする集合と考え、2つの集合間の和、差、共通集合を求めます。例えば、以下のprod表とprod2表について考えます。

prod表

prod_id	prod_name	price
1	みかん	50
2	りんご	70
3	メロン	100
4	バナナ	30

prod2表

prod_id	prod_name	price
1	みかん	50
3	メロン	100
5	ぶどう	50

prod表とprod2表の和集合、差集合、共通集合は以下のようになります。

和集合

prod_id	prod_name	price
1	みかん	50
2	りんご	70
3	メロン	100
4	バナナ	30
5	ぶどう	50

prod表とprod2表のどちらかにある行はすべて出力されます。

差集合(prod - prod2)

prod_id	prod_name	price
2	りんご	70
4	バナナ	30

prod表にあってprod2表にない行のみが出力されます。

共通集合

prod_id	prod_name	price
1	みかん	50
3	メロン	100

prod表とprod2表の両方にある行のみが出力されます。

集合演算を行う際には、以下の句を使用します。

• UNION句は、和集合を求めます。
• EXCEPT句は、差集合を求めます。
• INTERSECT句は、共通集合を求めます。

各句で指定が必要なSELECT文は、集合演算を行う相手の表を生成する問い合わせです。いずれの場合も、2つの表間で列数とデータ型が一致している必要がありますが、列名は異なっていても構いません。オプションの DISTINCT | ALL は重複した行を除去するかしないかの指定で、省略時はDISTINCTで重複した行が除去されます。

集合演算の具体例として、prod表とprod2表の和集合を出力するSELECT文を以下に示します。

SELECT prod_id, prod_name, price FROM prod
    UNION
    SELECT prod_id, prod_name, price FROM prod_2;

 

結合演算は、2つの表を横方向に結合します。結合することによって、別の表から関連するデータを取得することができます。結合方法には、内部結合、外部結合、クロス結合の3種類があります。内部結合は、多くの場合に単に結合と呼ばれます。

内部結合は、2つの表の共通の列(結合キー)に着目して、列の値が等しい行どうしを結合した表を出力します。以下の例は、orders表とprod2表をprod_id列で内部結合した結果です。内部結合では相手の表に結合できる行が存在しない行は出力されません。

orders					prod2
order_id	order_date	customer_id	prod_id	qty	prod_id	prod_name	price
1	2024-04-01	1	1	10	1	みかん	50
3	2024-04-01	3	3	8	3	メロン	100
4	2024-04-02	2	1	3	1	みかん	50

外部結合は、内部結合の結果に加えて、相手の表に結合できる行が存在しない行も出力します。外部結合には、左側の表の行をすべて出力する左外部結合、右側の表の行をすべて出力する右外部結合、両方の表の行をすべて出力するフル外部結合の3種類があります。以下の例は、orders表とprod2表をprod_id列でフル外部結合した結果です。

orders					prod2
order_id	order_date	customer_id	prod_id	qty	prod_id	prod_name	price
1	2024-04-01	1	1	10	1	みかん	50
2	2024-04-01	2	2	5
3	2024-04-01	3	3	8	3	メロン	100
4	2024-04-02	2	1	3	1	みかん	50
5	2024-04-02	3	2	4
					5	ぶどう	50

クロス結合は、結合キーは使用せず、2つの表の行の組み合わせすべてを出力します。以下の例は、prod表とprod2表をクロス結合した結果です。

prod			prod2
prod_id	prod_name	price	prod_id	prod_name	price
1	みかん	50	1	みかん	50
1	みかん	50	3	メロン	100
1	みかん	50	5	ぶどう	50
2	りんご	70	1	みかん	50
2	りんご	70	3	メロン	100
2	りんご	70	5	ぶどう	50
以下省略

クロス結合は一見無意味な操作を行っているように見えるかもしれませんが、WHERE句と組み合わせることにより意味のある検索を行うことができます。

結合演算を行う場合は、FROM句を以下のように記述します。

    FROM 表名1 [[AS] 別名]
        結合方法 表名2 [[AS] 別名] [ON 結合条件] [結合方法 ...]

• 表名1は、結合する最初の表の名前です。
  参照目的で別の名前を付けることができます。
• 結合方法は、以下のいずれかを指定します。
  [INNER] JOIN --- 内部結合、結合条件を満たす行のみを出力
  LEFT [OUTER] JOIN --- 左外部結合、結合条件を満たさない左側の表の行も出力
  RIGHT [OUTER] JOIN --- 右外部結合、結合条件を満たさない右側の表の行も出力
  FULL [OUTER] JOIN --- フル外部結合、結合条件を満たさない左右両側の表の行も出力
  CROSS JOIN --- クロス結合、左右両側の表の行のすべての組み合わせを出力
• 表名2は、結合する2番目以降の表の名前です。
  参照目的で別の名前を付けることができます。
• 結合条件は、結合する行の条件を指定します。
  クロス結合以外は必ず指定する必要があり、クロス結合では指定できません。 結合条件は論理式で、この論理式が真となるすべての行が出力されます。論理式については別の節で説明します。

FROM句で、結合方法を指定せずに表名を単純にカンマで区切って複数指定した場合にはクロス結合が行われます。WHERE句は、結合後の行を選択する条件となります。

結合演算の具体例として、orders表とprod表をprod_id列で内部結合するSELECT文を以下に示します。prod_id列はorders表とprod表の両方にあるため、どちらを指すのか明確にするため 表名.列名 の形式で記述します。

SELECT order_id, order_date, customer_id, prod.prod_id, prod_name, qty
    FROM orders
        JOIN prod ON orders.prod_id = prod.prod_id;

上記のSELECT文は、クロス結合とWHERE句を使って以下のように書き直すことができます。

SELECT order_id, order_date, customer_id, prod.prod_id, prod_name, qty
    FROM orders, prod
    WHERE orders.prod_id = prod.prod_id;

次の例は、orders表とprod表をprod_id列で右外部結合するSELECT文です。prod表のすべての行が結果に現れます。

SELECT order_id, order_date, customer_id, p.prod_id, prod_name, qty
    FROM orders AS o
        RIGHT JOIN prod AS p ON o.prod_id = p.prod_id;

次の例は、orders表とprod表をprod_id列で結合し、さらにcustomer表をcustomer_id列で結合するSELECT文です。

SELECT order_id, order_date, customer_name, prod_name, qty
    FROM orders AS o JOIN prod p ON o.prod_id = p.prod_id
        JOIN customer AS c ON o.customer_id = c.customer_id;

次の例は、結合条件に不等号を使ったSELECT文です。prod表のpriceがprod2表のprice列よりも大きい行を出力します。このSELECT文は、「みかん」よりも価格が高い商品の名前を出力します。

SELECT p1.prod_name
    FROM prod AS p1
        JOIN prod2 AS p2 ON p1.price > p2.price
    WHERE p2.prod_name = 'みかん';

上記のSELECT文は、クロス結合とWHERE句を使って以下のように書き直すことができます。

SELECT p1.prod_name
    FROM prod AS p1, prod2 AS p2
    WHERE p1.price > p2.price AND p2.prod_name = 'みかん';