SQL指南書　HAVING句④　NULLを含まない集合を探す

SQL 指南書集目次 - ray88’s diary
■COUNT(*)とCOUNT(列名)の違い
　・COUNT関数の使用法にはCOUNT(*)とCOUNT(列名)がある
　・COUNT(*) はNULLを数えるのに対し、 COUNT(列名)は他の集計関数と同様に NULL除外して集計する
　　※つまり、COUNT(*)は全行を数えるのに対しCOUNT(列名)はそうではない
　（例）NULL値しか含まないテーブルに以下のSELECT文を実行する

--NULLを含む列に適用した場合、COUNT(*)とCOUNT(列名)の結果は異なる
SELECT COUNT(*),COUNT(col_1)
	FROM null_tbl;

出力結果

■例題
　学生のレポート提出日を記録するテーブル「students」がある。
　学生がレポートを未提出の間の提出日列の値はNULL。提出すると提出日が入る。
　このテーブルより所属するすべての学生が提出済の学部（理学部と経済学部）を求める

【考え方】
STEP1：学部をキーにGROUP BY句を使用して下図のような部分集合を作る
STEP2：作成した４つの部分集合のうち、抽出するのはS1(理学部)とS4(経済学部)
STEP3：S1(理学部)とS4(経済学部)の２つの集合が共有していて他の集合がもっていない性質を探す
　　　→「COUNT(*)とCOUNT(sbmt_date)」が一致する（S3とS4はNULLを含むため一致しない）

以上より、以下回答のSQL文となる
【回答例】
その１：COUNT関数の利用

--提出日にNULLを含まない学部を選択する　その１：COUNT関数の利用
SELECT dpt
	FROM students
	GROUP BY dpt
	HAVING COUNT(*) = COUNT(sbmt_date);

その２：CASE式の利用
※Case式は提出日がNULLでない行については１，NULLの行については０というフラグを立てている

--提出日にNULLを含まない学部を選択する　その１：COUNT関数の利用
SELECT dpt
	FROM students
	GROUP BY dpt
	HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL
						 THEN 1
						 ELSE 0 END);

■応用例題
全員が９月中にレポート提出済みの学部を抽出する
※経済学部のみが抽出される。
　理学部は１００番の学生が１０月に提出しているため除外
　文学部と工学部は未提出の学生がいるため除外

【回答】
　解答例１

/* 演習問題２：特性関数の練習(p.293) 
   全員が9 月中に提出済みの学部を選択する　その１：BETWEEN 述語の利用 */
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date BETWEEN '2005-09-01' AND '2005-09-30'
                           THEN 1 ELSE 0 END);

　解答例２

/* 演習問題２：特性関数の練習(p.293) 
   全員が9 月中に提出済みの学部を選択する　その２：EXTRACT 関数の利用 */
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN EXTRACT (YEAR FROM sbmt_date) = 2005
                            AND EXTRACT (MONTH FROM sbmt_date) = 09
                           THEN 1 ELSE 0 END);

【参考】テーブル作成SQL
null_tbl

CREATE TABLE null_tbl
(col_1 VARCHAR(16));

INSERT INTO null_tbl VALUES(NULL);
INSERT INTO null_tbl VALUES(NULL);
INSERT INTO null_tbl VALUES(NULL);

students

/* NULL を含まない集合を探す */
CREATE TABLE Students
(student_id   INTEGER PRIMARY KEY,
 dpt          VARCHAR(16) NOT NULL,
 sbmt_date    DATE);

INSERT INTO Students VALUES(100,  '理学部',   '2005-10-10');
INSERT INTO Students VALUES(101,  '理学部',   '2005-09-22');
INSERT INTO Students VALUES(102,  '文学部',   NULL);
INSERT INTO Students VALUES(103,  '文学部',   '2005-09-10');
INSERT INTO Students VALUES(200,  '文学部',   '2005-09-22');
INSERT INTO Students VALUES(201,  '工学部',   NULL);
INSERT INTO Students VALUES(202,  '経済学部', '2005-09-25');