PostgreSQLのNULL値の挙動について

PostgreSQLのNULL値を使用した時の挙動についてのまとめです。

検証したPostgreSQLのバージョン

psqlバージョン:psql (PostgreSQL) 9.6.10


NULL値のある項目の並び替えについて

NULL値を含む項目を「ORDER BY」句で「ASC」で指定するとNULL値は最後に表示される

NULL値の並びは実行してみないとわからない

※データの並びを変えるとNULL値の値の順番が変化するのでどういう順番なのかよくわからない

-- NULL値を含むデータを作成

SELECT *
FROM (
SELECT NULL AS animal, 0 AS no
UNION ALL SELECT '1_ライオン' AS animal, 1 AS no
UNION ALL SELECT NULL AS animal, 6 AS no
UNION ALL SELECT '4_ゴリラ' AS animal, 5 AS no
UNION ALL SELECT NULL AS animal, 3 AS no
UNION ALL SELECT '3_アリクイ' AS animal, 4 AS no
UNION ALL SELECT '' AS animal, 7 AS no
UNION ALL SELECT '2_ぞう' AS animal, 2 AS no
UNION ALL SELECT '' AS animal, 8 AS no
) AS jikken_data
ORDER BY animal ASC
;

SQLの結果

animal
no

7

8

1_ライオン
1

2_ぞう
2

3_アリクイ
4

4_ゴリラ
5

NULL
3

NULL
6

NULL
0

NULL値を含む項目を「ORDER BY」句で「DESC」で指定するとNULL値は最初に表示される

NULL値の並びは実行してみないとわからない

※データの並びを変えるとNULL値の値の順番が変化するのでどういう順番なのかよくわからない

-- NULL値を含むデータを作成

SELECT *
FROM (
SELECT NULL AS animal, 0 AS no
UNION ALL SELECT '1_ライオン' AS animal, 1 AS no
UNION ALL SELECT NULL AS animal, 6 AS no
UNION ALL SELECT '4_ゴリラ' AS animal, 5 AS no
UNION ALL SELECT NULL AS animal, 3 AS no
UNION ALL SELECT '3_アリクイ' AS animal, 4 AS no
UNION ALL SELECT '' AS animal, 7 AS no
UNION ALL SELECT '2_ぞう' AS animal, 2 AS no
UNION ALL SELECT '' AS animal, 8 AS no
) AS jikken_data
ORDER BY animal DESC
;

SQLの結果

animal
no

NULL
0

NULL
6

NULL
3

4_ゴリラ
5

3_アリクイ
4

2_ぞう
2

1_ライオン
1

8

7


NULL値のある項目を条件にした時

今回は「1_ライオン」以外のデータを抽出しようとしたがNULL値のレコードは抽出されなかったため

NULL値を含む項目を「WHERE」句で指定するとNULL値は無視され表示されない

  SELECT * 

FROM (
SELECT NULL AS animal, 0 AS no
UNION ALL SELECT '1_ライオン' AS animal, 1 AS no
UNION ALL SELECT NULL AS animal, 6 AS no
UNION ALL SELECT '4_ゴリラ' AS animal, 5 AS no
UNION ALL SELECT NULL AS animal, 3 AS no
UNION ALL SELECT '3_アリクイ' AS animal, 4 AS no
UNION ALL SELECT '' AS animal, 7 AS no
UNION ALL SELECT '2_ぞう' AS animal, 2 AS no
UNION ALL SELECT '' AS animal, 8 AS no
) AS jikken_data
WHERE animal <> '1_ライオン'
ORDER BY animal
;

SQLの結果

animal
no

7

8

2_ぞう
2

3_アリクイ
4

4_ゴリラ
5


NULL値のある項目をCOUNTした時

NULL値の項目をCOUNTした時、0件と表示されたため

NULL値を含む値をCOUNTするとNULL値の項目はCOUNTされない

※ただし「COUNT(*)」にするとキリンのレコードは正しく2件と表示された

  SELECT name

, COUNT(value)
FROM (
SELECT 'ゴリラ' AS name ,NULL AS value
UNION ALL SELECT 'ゴリラ' AS name ,1 AS value
UNION ALL SELECT 'ゴリラ' AS name ,5 AS value
UNION ALL SELECT 'ゴリラ' AS name ,NULL AS value
UNION ALL SELECT 'キリン' AS name ,NULL AS value
UNION ALL SELECT 'キリン' AS name ,NULL AS value
UNION ALL SELECT 'ゴリラ' AS name ,10 AS value
UNION ALL SELECT 'ゴリラ' AS name ,0 AS value
) AS jikken_data
GROUP BY name
;

SQLの結果

name
count

ゴリラ
4

キリン
0


NULL値のある項目をSUMした時

NULL値と数値を含む項目をSUMした時、NULL値は無視された正しく計算された

NULL値のみの項目をSUMした時、NULLと計算された

NULL値を含む値をSUMするとNULL値に関係なく正しくSUMされる

  SELECT name

, SUM(value)
FROM (
SELECT 'ゴリラ' AS name ,NULL AS value
UNION ALL SELECT 'ゴリラ' AS name ,1 AS value
UNION ALL SELECT 'ゴリラ' AS name ,5 AS value
UNION ALL SELECT 'ゴリラ' AS name ,NULL AS value
UNION ALL SELECT 'キリン' AS name ,NULL AS value
UNION ALL SELECT 'キリン' AS name ,NULL AS value
UNION ALL SELECT 'ゴリラ' AS name ,10 AS value
UNION ALL SELECT 'ゴリラ' AS name ,0 AS value
) AS jikken_data
GROUP BY name
;

SQLの結果

name
sum

ゴリラ
16

キリン
NULL


NULL値を含む値同士を文字列結合

NULL値と文字列結合した時、NULLと表示されたため

NULL値と文字列結合した時はNULLになる

SELECT *

FROM (
SELECT 'ゴリラ' || NULL AS name
UNION ALL SELECT NULL || 'ゴリラ' AS name
UNION ALL SELECT 'ゴリラ' || NULL || 'キリン' AS name
UNION ALL SELECT NULL || NULL AS name
) AS jikken_data
;

SQLの結果

name

NULL

NULL

NULL

NULL


NULL値を含む値同士を加減乗除

NULL値と加減乗除した時、NULLと表示されたため

NULL値を加減乗除した時はNULLになる

SELECT *

FROM (
SELECT 1 + NULL AS value
UNION ALL SELECT NULL + 2 AS value
UNION ALL SELECT 3 + NULL + 4 AS value
UNION ALL SELECT 1 - NULL AS value
UNION ALL SELECT NULL - 2 AS value
UNION ALL SELECT 3 - NULL - 4 AS value
UNION ALL SELECT 1 * NULL AS value
UNION ALL SELECT NULL * 2 AS value
UNION ALL SELECT 3 * NULL * 4 AS value
UNION ALL SELECT 1 / NULL AS value
UNION ALL SELECT NULL / 2 AS value
UNION ALL SELECT 3 / NULL / 4 AS value
) AS jikken_data
;

SQLの結果

value

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL


NULL値のCASE文

CASE式にNULL値が含まれているとそのNULL値の比較が出来ず「NULLだよ」が表示されないため

CASE式でNULL値の比較を行うことはできない

※COALESCEを使用してNULL値を変換すれば比較はできます

  SELECT CASE animal

WHEN 'ゴリラ' THEN 'ゴリラだよ'
WHEN 'ライオン' THEN 'ライオンだよ'
WHEN '' THEN '空文字だよ'
-- WHEN animal IS NULL THEN 'NULLだよ' -- この書き方だとエラーになる
-- WHEN IS NULL THEN 'NULLだよ' -- この書き方だとエラーになる
WHEN NULL THEN 'NULLだよ' -- ここをコメントにしても結果は同じである
ELSE animal
END
FROM (
SELECT NULL AS animal, 0 AS no
UNION ALL SELECT 'ライオン' AS animal, 1 AS no
UNION ALL SELECT NULL AS animal, 6 AS no
UNION ALL SELECT 'ゴリラ' AS animal, 5 AS no
UNION ALL SELECT NULL AS animal, 3 AS no
UNION ALL SELECT 'ゴリラ' AS animal, 4 AS no
UNION ALL SELECT '' AS animal, 7 AS no
UNION ALL SELECT 'キリン' AS animal, 2 AS no
UNION ALL SELECT '' AS animal, 8 AS no
) AS jikken_data
;

SQLの結果

animal

NULL

ライオンだよ

NULL

ゴリラだよ

NULL

ゴリラだよ

空文字だよ

キリン

空文字だよ