外部結合と WHERE 句の条件
部署ごとの在籍者数を，在籍者がいない部署は 0 として出力する意図で，次の SQL を作成した。表 dept と表 emp の内容が次のとき，この SQL の実行結果として適切なものはどれか。
dept emp
dept_id emp_id dept_id status
10 1 10 ACTIVE
20 2 20 ACTIVE
30 3 20 ACTIVE
40 4 30 RETIREDSELECT d.dept_id, COUNT(e.emp_id) AS cnt
FROM dept d LEFT OUTER JOIN emp e
ON d.dept_id = e.dept_id
WHERE e.status = 'ACTIVE'
GROUP BY d.dept_id
ORDER BY d.dept_id- ア(10, 1)，(20, 2) の 2 行
- イ(10, 1)，(20, 2)，(30, 0)，(40, 0) の 4 行
- ウ(10, 1)，(20, 2)，(40, 0) の 3 行
- エ(10, 1)，(20, 2)，(30, NULL)，(40, NULL) の 4 行
正解と解説を見る
正解 ア
この問題の基礎知識
- 左外部結合は，内部結合の結果に，結合相手のない左側の表の行を，右側の表の列を NULL として補って加える。
- ON 句の条件は結合の時点で評価され，WHERE 句の条件は結合後の結果に対して評価される。外部結合では，右側の表の列に対する条件をどちらに書くかで結果が変わる。
- SQL の比較演算は，一方が NULL なら真でも偽でもない不明（UNKNOWN）となる。WHERE 句は条件が真の行だけを残し，偽や不明の行は除く。
- COUNT(列) は NULL でない値の個数を数え，該当する値がなければ 0 を返す。SUM や AVG などは，対象の値が一つもなければ NULL を返す。
外部結合で部署 30 と社員 4 の行，部署 40 と NULL の行ができるが，WHERE 句は結合の後に評価される。status が 'RETIRED' の行と NULL の行は条件を満たさずに除かれるので，部署 30 と 40 は出力されない。
条件を ON 句に書いた場合（ON d.dept_id = e.dept_id AND e.status = 'ACTIVE'）の結果である。ON 句の条件は結合の時点で評価されるので部署 30 と 40 は NULL で補われた行として残るが，WHERE 句の条件は結合の後に評価される。
部署 40 の行の e.status は NULL であり，NULL = 'ACTIVE' の比較結果は真ではなく不明（NULL）となる。WHERE 句は条件が真となる行だけを残すので，NULL の行も RETIRED の行と同様に除かれる。
COUNT(列) は NULL でない値の個数を返し，対象の値がなくても NULL ではなく 0 を返す（NULL を返すのは SUM などの他の集約関数である）。そもそも部署 30 と 40 の行は WHERE 句によって除かれる。
照合の根拠（5 件）
- ア（正解）・イ（誤り）: WHERE 句の条件は結合の後に処理されるので，外部結合で補われた行も除かれ得る。イは条件を ON 句に書いた場合の結果
This is because a restriction placed in the ON clause is processed before the join, while a restriction placed in the WHERE clause is processed after the join.
PostgreSQL Documentation (queries.sgml) · Table Expressions — Joined Tables（新しいタブで開きます） - ア（正解）: LEFT OUTER JOIN で部署 40 には emp 側の列が NULL の行が追加される
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2.
PostgreSQL Documentation (queries.sgml) · Table Expressions — Joined Tables（新しいタブで開きます） - ア（正解）・ウ（誤り）: WHERE 句は条件が真の行だけを残し，偽又は NULL の行は捨てる。部署 30（RETIRED）も部署 40（NULL）も除かれる
If the result of the condition is true, the row is kept in the output table, otherwise (i.e., if the result is false or null) it is discarded.
PostgreSQL Documentation (queries.sgml) · Table Expressions — The WHERE Clause（新しいタブで開きます） - ウ（誤り）: NULL = 'ACTIVE' は真ではなく NULL（不明）になる
Ordinary comparison operators yield null (signifying unknown ), not true or false, when either input is null.
PostgreSQL Documentation (func-comparison.sgml) · Comparison Functions and Operators（新しいタブで開きます） - エ（誤り）: count は対象がなくても NULL を返さない（NULL を返すのは count 以外の集約関数）
It should be noted that except for count , these functions return a null value when no rows are selected.
PostgreSQL Documentation (func-aggregate.sgml) · Aggregate Functions（新しいタブで開きます）
検算: 結合結果は (10,1,ACTIVE)，(20,2,ACTIVE)，(20,3,ACTIVE)，(30,4,RETIRED)，(40,NULL,NULL)。WHERE e.status = 'ACTIVE' で先頭 3 行だけが残り，GROUP BY で (10, 1)，(20, 2) の 2 行になる。