間違いやすいSQLのコマンド5選

SQLを書いていて構文エラーは出ないけど思ってた出力結果が違う！
っていうことは多々あると思いますが、
間違えても気付きにくいコマンドを紹介していきます。

※この記事はPostgreSQL 13.5 で動作確認してます。

文字列の結合

--やりがちな例
SELECT 市区町村名 || 番地 FROM 住所一覧

よくある形なんですが、結合する文字列にNULLがあるとNULLを返します。
NULLが含まれる可能性がある文字列を結合する場合はCONCAT関数を使いましょう。
もしくはCOALESCE関数を使ってNULLを置換することで回避できます。

CONCAT関数はNULLの場合無視され、全てNULLの場合は空文字を返します。
COALESCE関数はNULLを別の文字に置換することでNULLを回避します

結合するカラムが多くなるとCOALESCE関数は遅くなるのでCONCAT関数がおススメです。
読みやすいし。

--推奨例
SELECT concat(市区町村名,番地) FROM 住所一覧
SELECT coalesce(市区町村名,'') || coalesce(番地,'') FROM 住所一覧

timestamp型の検索

--やりがちな例
SELECT * FROM 商品マスタ WHERE 登録日時 BETWEEN '2023/01/01' AND '2023/01/31'
（登録日時はtimestamp型）

これもやりがちなんですが、timestamp型の場合は注意が必要です。
timestamp型に対して日付のみで指定した場合は時刻が 0:00:00 で検索条件が指定されます。
なので上の条件で検索をかけると実際には下記の条件で検索をかけていることになります。
31日の分が丸々一日検索範囲外になってしまうんですね。

WHERE 登録日時 BETWEEN '2023/01/01 0:00:00' AND '2023/01/31 0:00:00'

対応策としてはtimestamp型をDATE型にcastしてから日付で検索、
もしくはちゃんと時刻まで指定するという形になります。

--推奨例
SELECT * FROM 商品マスタ WHERE CAST(登録日時 AS DATE) BETWEEN '2023/01/01' AND '2023/01/31'
SELECT * FROM 商品マスタ WHERE 登録日時 BETWEEN '2023/01/01 0:00:00' AND '2023/01/31 23:59:59'

文字列の置換

--やりがちな例
REGEXP_REPLACE('ああいいうう','あ','ア')  --結果：アあいいうう

最初に見つかった文字列しか置換かけてくれません。
全部一括で置換掛けたい時は第四引数に「g」を追加します。
そうすると文字列全体に対して置換かけてくれます。

--推奨例
REGEXP_REPLACE('ああいいうう','あ','ア','g')  --結果：アアいいうう

整数の割り算

--やりがちな例
select 1 / 2  -- 結果：0

整数同士で割り算すると整数型のまま計算されるようで小数点は切り捨てられて0になります。
対策としては先にCASTをかけて整数型から小数点型に変更することで小数点も取得できるようになります。
または、先に1.0をかけると整数型が勝手に小数点型になります。こんなんありなん……

--推奨例
select cast(1 AS FLOAT) /2  -- 結果：0.5
select (1 * 1.0) / 2  -- 結果：0.5

IN句、GROUP BY句の大文字小文字判定

GROUP BYで集計した時、大文字小文字は区別されて集計されます。
そのため、大文字小文字を区別しないで集計したい場合はGROUP BY時にLOWER または UPPER で変換をかける必要があります。
特にHAVINGで件数チェックしてる場合は気付きにくいので注意です。

--推奨例
--メールアドレスが複数登録されているかチェック
SELECT LOWER(email) AS email_low ,COUNT(email) AS cnt
FROM 顧客テーブル
GROUP BY LOWER(email)
HAVING COUNT(email) > 1
ORDER BY LOWER(email)