【これを知らずにSELECTは語れない】SQLのSELECTの力を引き出すための詳細文法

SELECT

リレーショナルデータベースからデータを参照するために使います。

SQLの大部分はSELECTと言っていいほどSQLでかなり重要な部分です。

構文

SELECT [ALL | DISTINCT ]
    表示する列のリスト
FROM 表1 [, ...表n]
[JOIN　結合する表　ON　結合条件]
[WHERE 選択条件]
[GROUP BY グループ化するための列のリスト]
[HAVING グループ化後の選択条件]
[ORDER BY 並び替えのキーとなる列のリスト[ASC | DESC]] 

評価順序

SQLは、C言語やJava（手続き型言語、オブジェクト指向型言語）などとは違い、上から評価されません。この違いのために、間違って解釈したり、思った通りにSQLを記述できない場合があります。

その間違いを減らすために

評価順序を確認する必要があります。以下の表の上から順番に評価されます。

SELECTへの指定と結果

SELECT
    出金額,          -- 列名での指定
    出金額 + 100,    -- 計算式での指定
    'SQL',          -- 固定値での指定
    出金額 AS '支出' -- 列に別名をつける
  FROM 家計簿;

SELECT
    *    -- 全ての列を指定
  FROM 家計簿;

列名:FROMで指定されたテーブルの列の内容がそのまま出力される
計算式:計算式の評価結果が出力される
固定値:固定値がそのまま出色される
※ 出力結果の列名は、SELECTで指定した形になる。（ASで列や表名、副問い合わせなど別名をつけることができます。）
* (アスタリスク)で列名を指定した場合は、表の全ての列を指定したことになります。

SELECT文につけるキーワード

SELECT内で用いる関数

DBMSによって関数の動作は大きく変わるので注意が必要です。

以下は、関数名しか書いてませんので引数は各自で調べてみてください

COALESCE　データの欠損値をデフォルトに置き換え
CONCAT　文字列連結
LENGTH, LEN　長さを得る
TRIM　空白を除去する
REPLEACE　指定文字を置換する
SUBSTRING, SUBSTR　一部を抽出する
ROUND　指定桁で四捨五入
TRUNC　指定桁で切り捨てる
POWER　べき乗
CAST, CONVERT　データ型を変換する
COALESCE　最初に登場するNULLでない値を返す

SELECT
    COALESCE(費目, '不明')   -- 費目がNULLのものを’不明’にする
  FROM 家計簿;

※　ユーザー定義関数とストアドプロシージャ
ユーザー定義関数：あらかじめ用意された関数だけでなく、必要とする処理を自分で記述して作成した関数をsqlから利用することができる。

ストアドプロシージャ:実行する複数のSQL文をまとめ、プログラムのようなものとしてDBMS内に保存し、データベースの外部から呼び出すもの

ユーザー定義関数やストアドプロシージャは、DBMS製品ごとに定められたプログラミング言語を使って記述する。例えば、Oracle DBだとPL/SQL、SQL ServerだとTransact-SQLという専用言語を用いる。またCやJavaのような一般的なプログラミング言語による記述をサポートしているDBMS製品も存在する。

SELECT内で用いる演算子ーCASE演算子(値を変換する)

/* 単純CASE式 */
SELECT
    CASE 評価する列や式 
       WHEN 値1 THEN 値1の時に返す値
       [WHEN 値2 THEN 値2の時に返す値 ...]
       [ELSE デフォルト値]
    END
FROM テーブル名（ビュー名）

/* 検索CASE式 */
SELECT
    CASE
        WHEN 条件1 THEN 条件1の時に返す値
        [WHEN 条件2 THEN 条件2の時に返す値 ...]
        [ELSE デフォルト値]
    END
FROM テーブル名（ビュー名）

集約関数（集計関数）

AVG(列名)　平均値
MAX(列名)　最大値
MIN(列名)　最小値
SUM(列名)　合計値
COUNT(列名)　NULLを除外した行数
COUNT(＊)　NULLを含んだ行数

集計関数が記述できる場所

SELECT　○
FROM　x
WHERE　x
HAVING　○
ORDER BY　○

集計関数におけるNULLのとり扱い

SELECT内で用いる関数ーウィンドウ関数

リレーショナルデータベースでは、行に順序がなく、集合として演算を行います。しかし、データ分析を行う際などには、データの順序を取り扱ったり、集計する範囲を指定する必要があるためウィンドウ関数が導入されました。

SELECT
      AVG(列名) OVER(
      [PARTITION BY ウィンドウを分割する列名リスト]
      [ORDER BY 整列列名リスト]
      [フレーム句])
FROM テーブル名

順番を扱うウィンドウ関数専用の関数

従来の集約関数がウィンドウ関数としても使用できる関数

フレーム句

フレーム句は、ウィンドウ関数の中でのみ使用できる範囲指定である [ROWS|RANGE] BETWEEN 開始点 AND 終了点
ROWS句で始まる場合には、行単位で指定を行う。RANGE句で始まる場合は列の値の単位での指定が可能になる。

副問い合わせ（入れ子、副照会、サブクエリ、SQLのネスト）

副問い合わせの結果3つのパターン

• スカラ:　1行1列
• ベクター:　m行1列
• マトリックス:　m行n列　

SELECT：スカラ

SELECT 日付, メモ, 出金額,
       (SELECT 合計 FROM 家計簿集計
         WHERE 費目 = '食費') AS 過去の合計額
  FROM 家計簿アーカイブ
 WHERE 費目 = '食費';

副問い合わせの結果から確実にNULLを除外する方法

-- IS NOTを使う 
SELECT * FROM 家計簿アーカイブ
 WHERE 費目 IN (SELECT 費目 FROM 家計簿
                WHERE 費目 IS NOT NULL);

-- CALESCEを使う
SELECT * FROM 家計簿アーカイブ
 WHERE 費目 IN (SELECT COALESCE(費目, '不明') FROM 家計簿);

行値式と副問い合わせ

ここでは、n行1列の検索結果が返る副問い合わせを複数ぎょう副問い合わせとしたが、Oracle DBなどの一部のDBMSでは、結果が複数行、複数列でも、複数行副問い合わせとしての利用が可能。その場合は、複数の列を組み合わせて同時に比較することができる。この複数の列の列の組み合わせによる条件式を行値式という。

FROM

SELECT内で使用するテーブルを使用します。

テーブルを指定する時の注意点

SELECT 選択列リスト
FROM テーブルA, テーブルB

FROM句に複数のテーブルを指定した場合は、暗黙に直積になるので注意が必要です。

副問い合わせ

FROM：マトリックス

SELECT SUM(SUB.出金額) AS 出金額合計
  FROM (SELECT 日付, 費目, 出金額
          FROM 家計簿
        UNION
        SELECT 日付, 費目, 出金額
          FROM 家計簿アーカイブ
         WHERE 日付 >= '2018-01-01'
           AND 日付 <= '2018-01-31') AS SUB;

副問い合わせに別名をつける時の注意点 FROM句に記述した副問い合わせに別名をつけることを推奨します。SQL Serverなどの一部のRDBMSでは、別名が必須な場合もあります。

JOIN

テーブルを丸ごとつなぐことではなく、結合条件が満たされた行を1つ1つつなぐこと

結合の種類

• 内部結合（inner join） 結合する属性の値が二つの関係で一致するもののみを取り出す結合
  • θ結合 直積から二つの属性X,Yを取り出し[X θ Y]が成り立つタプルを選択演算したもの（θは比較演算）
  • 等結合 θ結合のうち、θが＝のを選んだもの
  • 自然結合 等結合から二つの関係の共通属性を一つのぞいて射影演算したもの
• 外部結合（outer join） 結合する属性の値が二つの関係で一致するものがない場合にも取り出す結合
  • 左外部結合（left (outer) join） 左側の関係の行は全て取り出し、右側の関係は結合条件に一致するものを取り出し結合する。存在しない列には、 NULLを挿入する結合
  • 右外部結合（right (outer) join） 右側の関係の行は全て取り出し、左側の関係は結合条件に一致するものを取り出し結合する。存在しない列には、 NULLを挿入する結合
  • 完全外部結合（full (outer) join） 共通属性に関し、どちらかの列に存在する行を全て取り出す結合

※ イコール以外の結合条件式（非等価結合） 動作の仕組みは通常の結合と一緒だが、DBMSにかかる負荷が大きくなることが多い

SELECT ~ FROM テーブルA
JOIN テーブルB
ON テーブルA.列名 > テーブルB.列名

FULL JOIN をUNIONで代用する

MySQL, MariaDBなどでは、集合演算子UNIONを使って同等の処理を実現することができる

SELECT 選択列リスト FROM 左表の名前
LEFT JOIN 右表の名前
ON 左表の結合条件列 = 右表の結合条件列
UNION
SELECT 選択列リスト FROM 左表の名前
RIGHT JOIN 右表の名前
ON 左表の結合条件列 = 右表の結合条件列

JOIN句を使わない結合

SELECT 選択列リスト
FROM テーブルA, テーブルB
WHERE 両テーブルの結合条件

WHERE

行を絞る条件を指定する。

WHERE句で使われる検索条件

WHERE句は、必ず真か偽になる式しか記述できない。
→ 1行ずつ順番に条件にあうかどうかをチェックするため

ワイルドカードも用いて曖昧検索をする

LIKE演算子で%や_を含む文字列探したい場合 100%という文字列で終わるかどうかを判別したい場合はESCAPE句を用いるか、エスケープシーケンスを用いる。

SELECT * FROM 家計簿 WHERE メモ LIKE '%100$%' ESCAPE '$'

同じ意味になる演算子

NOT IN と　<>ALLは全ての値と一致しないことを判定する演算子
IN と =ANYはいずれかの値と一致することを判定する演算子

INとEXISTSの違い

●INの場合
WHEREと【IN】の間に列名の指定が必要。
つまりWHERE 列名【IN】(SELECT～)・・・となる。

●EXISTの場合
【EXIST】のみ。
つまりWHERE 【EXIST】(SELECT～)・・・になる。

単独で処理できない副問い合わせ（相関副問い合わせ）

SELECT 列 FROM テーブル1
WHERE EXISTS
(SELECT * FROM テーブル1,テーブル2 WHERE テーブル1.列 = テーブル2.列)

他のテーブルに値が登場する行のみを抽出したい場合は、以上のような、副問い合わせの内部から主問い合わせの表や列を利用する副問い合わせする。この問い合わせを相関副問い合わせという。

※ 相関副問い合わせは副問い合わせの一種ではあるが、その処理方法や動作原理は一般的な副問い合わせとは根本的に異なる。通常の副問い合わせが『内側の副問い合わせを一回処理→主問い合わせを一回処理』という単純な処理であるのに対して、相関副問い合わせは、『外側SQLでテーブルから行を絞り込む過程で、各行について抽出して良いかを判断するために、繰り返し副問い合わせを実行する』ため、DBMSの負荷は大幅に増加する

副問い合わせ

WHERE（INでの利用）：ベクター

SELECT 
    *
  FROM 家計簿集計
  WHERE 費目 IN (SELECT DISTINCT 費目 FROM 家計簿);

WHERE（INでの利用）：マトリックス

SELECT
    *
  FROM 
  WHERE () IN 

WHERE（ANY/ALLでの利用）：ベクター

SELECT * FROM 家計簿
 WHERE 費目 = '食費'
   AND 出金額 < ANY (
                     SELECT 出金額 FROM 家計簿アーカイブ
                     WHERE 費目 = '食費');

WHERE（ANY/ALLでの利用）：マトリックス

WHERE（EXISTでの利用）：マトリックス

GROUP BY

テーブルを丸ごとつなぐことではなく、結合条件が満たされた行を1つ1つつなぐこと

指定した基準で検索結果をいくつかのまとまりに分ける

グループ集計を行うSELECT文の選択列リストに指定する列は、次のどちらかに当てはまるものではないと行けない
1)GROUP BYでグループ化の基準列として指定されている
2)集計関数による集計の対象になっている

HAVING

GROUP BYでしたグループに対しての絞り込みを行います。 SELECTの評価順序にある通り、GROUP BYのあとにHAVINGが評価されます。 WHEREでグループ化後の絞り込みができないのもGROUP BYが評価されていないと言う理由です。 逆に、WHEREで記述される条件はHAVINGでも指定できます。（同じ結果になるとは限りませんので注意が必要です。）

無駄な集計に御用心

次の例は、出費回数が5回以上の費目について、合計額と回数を求めるSQLで、正しく動作する。最後のANDで指定している費目の名称による絞り込みはWHERE句に書いても同じ結果表になることが理解できるでしょう。HAVING句ではなくWHERE句で、この絞り込みを行えばDBMSが処理する行数が減るためにSQLのパフォーマンスは向上する

SELECT 費目,
       SUM(出金額) AS 合計額,
       COUNT(出金額) AS 回数
  FROM 家計簿
 WHERE 出金額 > 0
 GROUP BY 費目
HAVING COUNT(出金額) >= 5
   AND 費目 IN ('食費', '居住費')  -- データ絞り込み条件

ORDER BY

出力を列の値をもとに並び替えを行います。

SELECT
    *
  FROM 家計簿
  ORDER BY 出金学 ASC, 残高 DESC

列名の後ろに何も指定しないもしくはASCを指定すると昇順になります。
DESCを指定すると降順になります。

先を知るための本

SQL実践入門──高速でわかりやすいクエリの書き方 （WEB+DB PRESS plus） [ ミック ]

created by Rinker

• Amazon
• 楽天市場
• Yahooショッピング