何も存在しないというNULLと存在すべきところに存在しない空文字とゼロ

本記事のポイント

数学的にいうと「何もない(NULL)に対してゼロという数字を与えた」ということになり、ゼロの持つ意味は数学的に位取りができること、哲学的にない状態があるという事実を表現することの2点になります。

例えば気温0度(ゼロ)に対して観測値なし(Null)、フォーマット済みのハードディスクの中身(空文字)に対して、ハードディスク自体が抜かれた状態(Null)になります。

SQLでは0除算はエラーですがNULL除算はNULLが返されエラーにならないため、値が0だったらNULLに置き換えます。

NULLのせいで意図しない結果にならないために

NULL は「何も存在しない」を意味するのであってゼロや空文字が意味する「存在しうるところに存在しない」とは違うものです。迷子がNULLでホームレスがゼロか空文字という例えで合っていると思います。

「ゼロを発見したのはインド人」とよく言われますが、数学的にいうと「何もない(NULL)に対してゼロという数字を与えた」ということになるのだと思います。ゼロがないと9から先の表現大変そう・・・・。

  1. 数学的に位取りができること
  2. 哲学的にない状態があるという事実を表現すること

SQLでは0除算はエラーですがNULL除算はNULLが返されエラーになりません。だからエラーを起こさないためには値が0だったらNULLに置き換えればいい訳で、このNULLIFはANSI標準のSQL関数ですべてのDBがサポートしています。

  • Access/SQLServer/Oracle/MySQL : NULLIF( VAL, 0 )

上述のようにSQLではNULLを含む加減乗除はNULLを返しますので、SUM集合関数を使って合計するときにNULLだと都合が悪いので、今度は逆にNULLなら0に変換してあげます。

  • Access : ISNULL(VAL, 0)
  • MySQL : IFNULL(VAL, 0)
  • Oracle : NVL (VAL, 0)

SQLではNULLとの加減乗除だけでなく比較演算子もNULLを返し、等号や不等号は成立しません。よってWHERE条件でNULLとの比較を行うときはIS NULLやIS NOT NULLを使う必要があります。

  • WHERE VAL=’0003′;
  • WHERE VAL<>’0003′;
  • WHERE VAL!=’0003′;
  • WHERE VAL IS NOT ‘0003’; エラー
  • WHERE VAL IS NULL; OK
  • WHERE VAL IS NOT NULL; OK
  • WHERE VAL != NULL; NULLを返す
  • WHERE VAL <> NULL; NULLを返す

DBごとに微妙に違うSQL構文

SQL文作成時にDBによって微妙に構文が異なっており、なんで統一してくんないの?といつも思うのですが、せっかくなのでよく使う構文だけまとめてみます。

日付

■日付型レコードをYYYYMM形式のテキストフォーマット出力

  • Access : format(dat, ‘YYYYMM’)
  • MySQL : DATE_FORMAT(dat, ‘%Y%m’)
  • Oracle : TO_CHAR(dat, ‘YYYYMM’)

■日付から年を取り出す

  • Access/SQLServer/MySQL : YEAR(dat)
  • Oracle : EXTRACT(YEAR FROM dat)

文字列

■文字列への型変換

  • Access : CStr(num)
  • SQLServer : CONVERT(VARCHAR, num)
  • Oracle : TO_CHAR(num)
  • MySQL : CAST(num AS CHAR)

■数値への型変換

  • Access : CInt(sgt)
  • SQLServer : CONVERT(INT, stg)
  • Oracle : TO_NUMBER(stg)
  • MySQL : CAST(stg AS INT)

■文字列の切り取り

  • Access : MID(stg, 3,2)
  • SQLServer/MySQL : SUBSTRING(stg, 3, 2)
  • Oracle : SUBSTR(stg, 3, 2)

■文字列の結合

  • Access/SQLServer : stg1 + stg2またはstg1 || stg2またはstg1 & stg2
  • MySQL/Oracle : CONCAT(stg1, stg2)

■文字列の置き換え

  • Access/SQLServer/Oracle : REPLACE(stg, ‘t’, ‘p’)
  • MySQL : TRANSLATE(stg, ‘t’, ‘p’)

■文字列の文字数取得

  • Access/SQLServer : LEN(stg)
  • Oracle : LENGTH(stg)
  • MySQL : CHAR_LENGTH(stg)