達人に学ぶSQL徹底指南書 1-3 3値論理とNULL

3値論理の真理表

(t = true, u = unknown, f = false)

• NOT

x	NOT x
t	f
u	u
f	t

• AND

AND	t	u	f
t	t	u	f
u	u	u	f
f	f	f	f

• OR

OR	t	u	f
t	t	t	t
u	t	u	u
f	t	u	f

排中律の不成立

テーブル定義

create table tbl1_3_1_Students(
    name nvarchar(10),
    age int
)

insert into tbl1_3_1_Students values
    ('ブラウン', 22),
    ('ラリー', 19),
    ('ジョン', null),
    ('ボギー', 21)

select * from tbl1_3_1_Students where age = 20 or age <> 20;
--ジョンの行が出力されない
--name age
--ブラウン 22
--ラリー    19
--ボギー    21

select * from tbl1_3_1_Students where age = 20 or age <> 20 or age is null;
--3値論理での条件網羅
--name age
--ブラウン 22
--ラリー    19
--ジョン    NULL
--ボギー    21

not in とnot existsの違い

テーブル定義

create table tbl1_3_2_ClassA(
    name nvarchar(10),
    age int,
    city nvarchar(10),
)

insert into tbl1_3_2_ClassA values
    ('ブラウン', 22, '東京'),
    ('ラリー', 19, '埼玉'),
    ('ボギー', 21, '千葉')

create table tbl1_3_2_ClassB(
    name nvarchar(10),
    age int,
    city nvarchar(10),
)

insert into tbl1_3_2_ClassB values
    ('斎藤', 22, '東京'),
    ('田尻', 23, '東京'),
    ('山田', NULL, '東京'),
    ('和泉', 18, '千葉'),
    ('武田', 20, '千葉'),
    ('石川', 19, '神奈川')

-- Bクラスの東京在住の生徒と年齢が一致しないAクラスの生徒を選択するSQL？
select * from tbl1_3_2_ClassA
where age not in (
    select age from tbl1_3_2_ClassB where city ='東京'
)
-- 結果がない

where age not in (...)はwhere age <> x1 and age <> x2 and ... と同義であるが、このときage <> nullという条件式があるとunknownが発生し、どのxに対しても"where unknown"という評価になり、結果が出力されない。

-- 本当にやりたかったこと
select * from tbl1_3_2_ClassA a
where not exists (
    select * from tbl1_3_2_ClassB b where a.age = b.age and b.city ='東京'
)
--name age city
--ラリー    19  埼玉
--ボギー    21  千葉

exists述語はtrueかfalseしか返さないため、unknownはfalseに評価される。

限定述語と極値関数の違い

ClassBのテーブルで東京在住の人の全ての人の年齢よりも低い年齢のClassAのテーブルの人を抽出する。

-- 限定述語 all を使う
select * from dbo.tbl1_3_2_ClassA
where age < all(
    select age from dbo.tbl1_3_2_ClassB 
    where city = '東京'
)
-- 結果の出力なし

限定述語の where age < all(...)はwhere age < x1 and age < x2 and ... と同義であるため、age < nullという条件式があると上記同様"where unknown"と評価され、結果が出力されない。

-- 極値関数 min を使う
select * from dbo.tbl1_3_2_ClassB
where age < (
    select min(age) from dbo.tbl1_3_2_ClassB 
    where city = '東京'
)
--name age city
--和泉   18  千葉
--武田   20  千葉
--石川   19  神奈川

極値関数はnullを集計に含めないため、nullを除いたageで最小値が求められる。ただし集計の対象がない場合はnullになるため注意。