【SQL】SQLのNULLの評価

2024年12月23日 11:52

こんにちは、スーパーソフトウェア東京オフィス技術部の金子です。
SQLのNULLの評価と出力結果に関してまとめました。

使用するテーブル

ドラえもんのキャラクターたちのテスト結果を例に動作を確認します。
（ジャイアンとのび太はテストを受けていないことにし、score を NULL にしています。。。）
テーブル名：test_results

$$
\begin{array}{|l|l|l|}
\textbf{id} & \textbf{ student\_name} & \textbf{score} \\ \hline
1 & 出来杉 & 100
\\ 2 & スネ夫 & 80
\\ 3 & 静香 & 70
\\ 4 & ジャイアン & NULL
\\ 5 & のび太 & NULL
\\ \end {array}
$$

CREATE文(MySQL)

CREATE TABLE test_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    score INT -- 点数 (NULL許容)
);

NULLの評価

DISTINCT

NULL は1つにまとめられる

SELECT DISTINCT score FROM test_results;

結果
 score 
-------
      
    70
    80
   100
(4 rows)

UPDATE

UPDATE で NULL に更新する場合は、「=」で代入する

UPDATE test_results SET score = NULL WHERE student_name = '静香';
SELECT * FROM test_results WHERE student_name = '静香';

結果
 id | student_name | score 
----+--------------+-------
  3 | 静香         |      
(1 row)

GROUP BY

NULL はグループ化される

SELECT score FROM test_results GROUP BY score;

結果
 score 
-------
      
    70
    80
   100
(4 rows)

ORDER BY

先頭か末尾にまとめられる
DBMS によっては先頭か末尾かを指定できる機能がある(NULLS FIRST, NULLS LAST)

SELECT * FROM test_results ORDER BY score DESC;

結果
 id | student_name | score 
----+--------------+-------
  4 | ジャイアン   |      
  5 | のび太       |      
  1 | 出来杉       |   100
  2 | スネ夫       |    80
  3 | 静香         |    70
(5 rows)

INSERTの省略カラム

省略したカラムにはデフォルト値が設定される
デフォルト値が設定されていない場合は、NULL が割り当てられる。省略したカラムに NOT NULL 制約がある場合はエラーになる

INSERT INTO test_results (student_name) VALUES ('ドラえもん');
SELECT * FROM test_results WHERE student_name = 'ドラえもん';

結果
 id | student_name | score 
----+--------------+-------
  6 | ドラえもん   |      
(1 row)

IN, NOT IN

NULL を選択することはできない

SELECT * FROM test_results WHERE score IN (NULL);

結果
 id | student_name | score 
----+--------------+-------
(0 rows)

SELECT * FROM test_results WHERE score NOT IN (100);

結果
 id | student_name | score 
----+--------------+-------
  2 | スネ夫       |    80
  3 | 静香         |    70
(2 rows)

算術演算

演算結果は NULL と評価される

SELECT 100 + NULL AS "演算結果";

結果
 演算結果 
----------
         
(1 row)

0の除算はエラーになるが NULL を0で除算する場合は NULL になる

SELECT NULL / 0 AS "演算結果";

結果
 演算結果 
----------
         
(1 row)

論理演算

比較演算子は使えない
演算結果は UNKOWN (不明) という値で評価される

SELECT * FROM test_results WHERE score = NULL;

結果
 id | student_name | score 
----+--------------+-------
(0 rows)

NULLを判定したい場合

IS NULL：NULLの行を選択

SELECT * FROM test_results WHERE score IS NULL;

結果
 id | student_name | score 
----+--------------+-------
  4 | ジャイアン   |      
  5 | のび太       |      
(2 rows)

IS NOT NULL：NULLでない行を選択

SELECT * FROM test_results WHERE score IS NOT NULL;
 
結果
  id | student_name | score 
----+--------------+-------
  2 | スネ夫       |    80
  1 | 出来杉       |   100
  3 | 静香         |    70
(3 rows)

集約関数

NULL はカウントされない

SELECT SUM(score) FROM test_results;

結果
 sum 
-----
 250
(1 row)

COUNT (*) は NULL をカウントする

SELECT COUNT(*) FROM test_results;

結果
 count 
-------
     5
(1 row)

COUNT (カラム名) は NULL はカウントしない

SELECT COUNT(score) FROM test_results;

結果
 count 
-------
     3
(1 row)

AVG関数で NULL を 0 としてカウントしたい場合は、AVG(COALESCE(カラム名, 0)) で平均値を算出する

-- score合計250をNULLを除いた3で除算
SELECT AVG(score) FROM test_results;

結果
         avg         
---------------------
 83.3333333333333333
(1 row)

-- score合計250をNULLを0として扱うことで分母に含め5で除算
SELECT AVG(COALESCE(score, 0)) FROM test_results;

結果
         avg         
---------------------
 50.0000000000000000
(1 row)

▼採用情報

▼新卒情報はWantedlyで