🙄

【SQL】式と関数

2025/08/05に公開

はじめに

以下は『スッキリわかるSQL入門第2版』で学習したことをメモとしてまとめました。
式と関数に関する基礎をまとめています。
なお、一部SQLの例文や文章を引用しております。

式

計算式: 列やリテラルを使った式で、結果が真または偽にならないもの
計算式を評価すると計算結果に化ける
計算式は、SELECT文の選択列リスト、INSERT文やUPDATE文での列に格納する値、その他の修飾句など、さまざまな場所で使用することができる

式と演算子

SELECTのすぐ後ろに指定するのが選択列リスト
選択列リスト：結果表にどのような列を出力するかを指定する役割
テーブルの列名以外にも、固定値や計算式を指定することも可能

選択列リストで計算式を使う

例

SELECT 出金額,        -- 列名での指定
       出金額 + 100,  -- 計算式での指定
       'SQL'         -- 固定値での指定
FROM 家計簿

列名：列の内容がそのまま出力される
計算式：計算式の評価結果が出力される
固定値：固定値がそのまま出力される

例

-- 計算式に別名をつけることができる
SELECT 出金額,
       出金額 + 100 AS 100円増の出金額
FROM 家計簿

選択列リストで計算式を使う場合は、必ずASを併用するようにするとわかりやすい

データの代わりに計算式を使う

INSERTやUPDATEで具体的なデータの代わりに式を指定する

INSERT文での計算式の利用

INSERT INTO 家計簿 (出金額)
     VALUES (1000 + 105)

UPDATE文での計算式の利用

UPDATE 家計簿
   SET 出金額 = 出金額 + 100

× 1回の処理で出金額列がすべて書き換えられる
○ 1行に注目しては出金額を計算して更新する、という処理を行数分繰り返す

式が評価される仕組み

◎ DBMSによる処理の原則

DBMSは、テーブル内の各行を1つずつ順番に処理していく
式の評価なども、各行ごとに行われる

DBMSが式を評価するときには常にいずれか1行だけに注目している
全部の行を1度に処理するのではなく、1行ずつ順番に処理される

さまざまな演算子

基本的な算術演算子

初めて見た演算子の使い方

演算子	使い方	説明
`+`	日付 + 数値	日付を指定日数分だけ進める
`-`	日付 - 数値	日付を指定日数だけ戻す
`-`	日付 - 日付	日付の差の日数を得る
`		`

※ DBMSによっては文字列の連結の演算子として「+」が利用されることもある

`CASE`演算子：値を変換する

列の値や条件式を評価し、その結果に応じて好きな値に変換することができる
式の結果に応じて複数のパターンの結果を得たい場合に有効

CASE演算子の基本構文

-- 1.
CASE 評価する列や式 WHEN 値1 THEN 値1のときに返す値
                 (WHEN 値2 THEN 値2のときに返す値) ...
                 (ELSE デフォルト値)
END

-- 2. 列名のすぐ後ろに列名や式を記述しない
-- WHENの後ろに値ではなく条件式を記述する
CASE WHEN 条件1 THEN 条件1のときに返す値
    (WHEN 条件2 THEN 条件2のときに返す値) ...
    (ELSE デフォルト値)
END

CASE演算子をつかったSELECT文(1)

SELECT 費目, 出金額
       CASE 費目 
           WHEN '居住費' THEN '固定費'
           WHEN '水道光熱費' THEN '固定費'
           ELSE '変動費'
       END AS 出費の分類
FROM 家計簿
WHERE 出金額 > 0

-- INでまとめた書き方
SELECT 費目, 出金額
       CASE
           WHEN 費目 IN ('居住費', '水道光熱費') THEN '固定費'
           ELSE '変動費'
       END AS 出費の分類
FROM 家計簿
WHERE 出金額 > 0

CASE演算子をつかったSELECT文(2)

SELECT 費目, 入金額
CASE WHEN 入金額 < 5000 THEN 'お小遣い'
     WHEN 入金額 < 100000 THEN '一時収入'
     WHEN 入金額 < 300000 THEN '給料出たー！'
     ELSE '想定外の収入です！'
END  AS 収入の分類
FROM 家計簿
WHERE 入金額 > 0

さまざまな関数

関数について定められていること

1. 名前
その関数の名前

2. 引数
その関数を呼び出す際に引き渡す情報（関数によっては2つ以上のこともある）

3. 戻り値
その関数の呼び出し結果として得られる情報

関数の呼び出し

関数の名前 (引数 ...)

例

SELECT メモ, LENGTH (メモ) AS メモの長さ
FROM 家計簿

関数が動作する流れ

◎ 関数の呼び出しも各行ごとに繰り返し行われる
※ 式の評価と同じ

◎ 関数の呼び出しの記述は、呼び出し完了後に戻り値に「化ける」

関数にまつわる注意点

関数はDBMSによって大きく異なる
関数は、DBMS製品ごとの違いが大きく、互換性が少ない分野

ユーザー定義関数とストアドプロシージャ

ユーザー定義関数

自分で記述して作成した関数をSQL文から利用する

ストアドプロシージャ

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

ユーザー定義関数やストアドプロシージャは、DBMS製品ごとに定められたプログラミング言語を使って記述する

多数のSQL文からなる処理を1つのストアドプロシージャにまとめることで、データベースとアプリケーション間のやり取りを少なくし、ネットワークの負荷を軽減できるといったメリットがある

文字列にまつわる関数

LENGTH / LEN : 長さを得る

文字列の長さを得る関数

LENGTH(文字列を表す列) => 文字列の長さを表す数値

LEN(文字列を表す列)    => 文字列の長さを表す数値

※ DBMS製品によって、文字数かバイト数になる

SQL ServerではLEN関数を利用する
（使用例）テーブルの列に格納されている文字列の長さを取得したり、文字列の長さで絞り込み検索を行ないたい場合に利用する

10文字(10バイト)以下のメモだけを取得

SELECT メモ, LENGTH(メモ) AS メモの長さ
FROM 家計簿
WHERE LENGTH(メモ) <= 10

TRIM : 空白を除去する

ある文字列の前後についている、余白を除去する

空白を除去する関数

TRIM(文字列を表す列)  => 左右から空白を除去した文字列

LTRIM(文字列を表す列) => 左側の空白を除去した文字列

RTRIM(文字列を表す列) => 右側の空白を除去した文字列

※ SQLの CHAR(n) 型は「固定長文字列型」なので、指定した文字数に満たないと、自動的に右側に空白（スペース）が追加されて埋められます。
このような文字列をSELECT文でそのまま抽出すると、abcの後ろに空白が付いた状態で取得することになる。

空白を除去したメモを取得

SELECT メモ, TRIM(メモ) AS 空白除去したメモ
FROM 家計簿

REPLACE : 指定文字を置換する

文字列の一部を別の文字列に置換する関数

文字列を置換する関数

REPLACE(置換対象の文字列, 置換前の部分文字列, 置換後の部分文字列)
 => 置換された後の文字列

※ 第一引数に列を使うと、その列の各行の値に対して置換が行われます

「置換対象の文字列」には、文字列リテラル（例: 'abc'）だけでなく、列（カラム）も指定できます。

メモの一部を置換する

UPDATE 家計簿
SET メモ = REPLACE(メモ, '購入', '買った')

SUBSTRING / SUBSTR : 一部を抽出する

文字列の一部分を取り出す
「何文字目から何文字分」という指定をする

文字列の一部を抽出する関数

SUBSTRING(文字列を表す列, 抽出を開始する位置, 抽出する文字の数)
 => 抽出された部分文字列

SUBSTR(文字列を表す列, 抽出を開始する位置, 抽出する文字の数)
 => 抽出された部分文字列

※ DBMS製品によって、文字数かバイト数になる

※ 抽出する文字の数を省略し、文字列の最後までを抽出対象とする場合もある

費目列の1~3文字目に「費」があるものだけを抽出する

SELECT *
FROM 家計簿
WHERE SUBSTRING(費目, 1, 3) LIKE '%費%'

CONCAT : 文字列を連結する

通常、文字列を連結するには、|| 演算子や + 演算子を使う

※ 連結できる文字列の数やNULLの扱いがDBMS製品によって異なるので注意

文字列を連結する関数

CONCAT(費目, ':' [, 文字列 ...])
=> 連結後の文字列

※ 1つでもNULLの文字列があるとNULLを返すDBMS製品もある

※ SQL ServerやMySQLなどでは3つ以上の文字列を指定することが可能

費目とメモを繋げて抽出する

SELECT CONCAT(費目, ':' || メモ)
FROM 家計簿

数値にまつわる関数

ROUND : 指定桁で四捨五入

指定桁で四捨五入する関数

ROUND(数値を表す列, 有効とする桁数)
=> 四捨五入した値

「有効とする桁数」

指定する値が正の場合: 小数部の桁数
指定する値が負の場合: 整数部の桁数

(例)100円単位の出金額を取得する

-- 出金額の下2桁目、10の位で四捨五入される --
SELECT 出金額, ROUND(出金額, -2) AS 100円単位の出費
FROM 家計簿

TRUNC : 指定桁で切り捨てる

指定桁で切り捨てる関数

TRUNC(数値を表す列, 有効とする桁数)
=> 切り捨てた値

「有効とする桁数」

指定する値が正の場合: 小数部の桁数
指定する値が負の場合: 整数部の桁数

POWER : べき乗を計算する

べき乗を計算する関数

POWER(数値を表す列, 何乗するかを指定する数値)
=> 数値を指定した回数だけ乗じた結果

日付にまつわる関数

CURRENT_DATE : 現在の日付を得る

CURRENT_TIME : 現在の時刻を得る

現在の日時を得る関数

CURRENT_DATE => 現在の日付(YYYY-MM-DD)
CURRENT_TIME => 現在の時刻(HH:MM:SS)

※ 引数が不要なため、関数の後ろに()をつけない

INSERT INTO 家計簿
VALUES (CURRENT_DATE, '食費', 'パンを購入', 0, 250)

変換にまつわる関数

CAST : データ型を変換する

列やリテラルにはデータ型がある

データ型を変換する関数

CAST(変換する値 AS 変換する型) => 変換後の値

(例)末尾に円を連結する

SELECT CAST(出金額 AS TEXT) || '円' AS 表示額
FROM 家計簿

※ 数値として解釈できない文字列をINTEGERに変換しようとするとエラーになるので注意

COALESCE : 最初に登場するNULLでない値を返す

複数の引数を受け取り、受け取った引数を左から順番にチェックし、その中から最初に見つかったNULLでない引数を返す

最初に登場するNULLでない値を返す

COALESCE(列や式1, 列や式2, 列や式3)
=> 引数のうち、最初に現れたNULLでない引数

引数は任意の数を指定可
ただし、すべての引数は型が一致している必要がある
もしすべての引数がNULLの場合、戻り値はNULL

COALESCE関数の基本動作

SELECT COALESCE('A', 'B', 'C');    -- 結果: 'A' --
SELECT COALESCE(NULL, 'B', 'C');   -- 結果: 'B' --
SELECT COALESCE(NULL, 'B', NULL);  -- 結果: 'B' --
SELECT COALESCE(NULL, NULL, 'C');  -- 結果: 'C' --
SELECT COALESCE(最初の列, 0); 
-- 結果: 数値型の列が出力される --
-- ただし、NULLが格納されている場合は0になる --

NULLの場合の代替値を決めることができる

列の値がNULLなら代わりにこの値を表示する

COALESCE(列名, NULLだったときの値) AS 別名

(例)NULLを明示的に表示する

SELECT 日付, 費目,
       COALESCE(メモ, '(メモはNULLです)') AS メモ,
       入金額, 出金額
FROM 家計簿

COALESCE() に渡せる「値」とは、&&NULL以外で表示したい代替データ**のこと
文字列、数値、日付文字列、関数の戻り値といったあらゆる型の値が対象になる。

※ COALESCE(メモ, '(メモはNULLです)') のようにエイリアス（AS 列名）を付けなかった場合、
その式自体が列名として出力されます。

エイリアスなしの表示

SELECT 日付, 費目,
       COALESCE(メモ, '(メモはNULLです)'),
       入金額, 出金額
FROM 家計簿

-- 結果の列名
COALESCE(メモ, '(メモはNULLです)')

そのまま関数の表記が列名になるので、見た目が読みにくくなる

SELECT 文は FROM 句なしでも書くことができる

「定数値や計算結果だけを取得したい」場合、SELECT文にFROM句がなくても成立する

例

SELECT 式や関数

計算式や関数はリテラルなどの具体的な値を材料にすれば、テーブル列を1つも記述しなくてもSELECT文として成り立つ

通常の SELECT 文では、テーブルから行を取得しますが、
このようなケースでは「テーブルを使わず、1回だけ何かを評価」するだけなので FROM が不要です。

注意（データベースによる違い）

SQLite, MySQL, PostgreSQL などでは FROM なしの SELECT が使えます。
SQL Server など一部のDBでは SELECT だけではダメで、FROM DUAL や FROM (SELECT 1) AS dummy などのダミー句が必要になる場合もあります。

参考文献

『スッキリわかるSQL入門第2版』