・SQLで全称量化を表現する方法 & NULLと付き合う上での注意点

DROP TABLE menus;

CREATE TABLE menus(
shop_name VARCHAR2(30),
drink VARCHAR2(50),
price NUMBER
);

INSERT INTO menus VALUES(‘A店’,’タピオカミルクティー’,400);
INSERT INTO menus VALUES(‘A店’,’タピオカ抹茶ミルクティー’,450);
INSERT INTO menus VALUES(‘A店’,’タピオカ豆乳’,500);
INSERT INTO menus VALUES(‘B店’,’タピオカミルクティー’,550);
INSERT INTO menus VALUES(‘B店’,’タピオカ抹茶ミルクティー’,600);
INSERT INTO menus VALUES(‘B店’,’タピオカジャスミンティー’,600);
INSERT INTO menus VALUES(‘C店’,’タピオカミルクティー’,450);
INSERT INTO menus VALUES(‘C店’,’タピオカラテ’,500);
INSERT INTO menus VALUES(‘C店’,’タピオカマンゴーミルクティー’,550);
COMMIT;

select * from menus;
SHOP_NAME DRINK PRICE
————— ————————————–
A店 タピオカミルクティー 400
A店 タピオカ抹茶ミルクティー 450
A店 タピオカ豆乳 500
B店 タピオカミルクティー 550
B店 タピオカ抹茶ミルクティー 600
B店 タピオカジャスミンティー 600
C店 タピオカミルクティー 450
C店 タピオカラテ 500
C店 タピオカマンゴーミルクティー 550
————— ————————————–

●全てのメニューが500円以下の店 -> NOT EXISTS
select * from menus t1
where not exists
(select * from menus t2
where t1.shop_name=t2.shop_name
and price > 500
)
;

●全てのメニューが500円以下の店 -> ALL
select * from menus t1
where 500 >= all
(select price from menus t2
where t1.shop_name=t2.shop_name
)
;

●全てのメニューが500円以下の店 -> HAVING
SELECT shop_name
FROM menus
GROUP BY shop_name
HAVING COUNT(*) = SUM(CASE WHEN price <= 500 THEN 1 ELSE 0 END); — 全てのメニューが500円以下の店 -> MAX
— ただしMAXにはALLやCOUNT(*)と違い、NULLを除外するという特徴あり。
SELECT shop_name
FROM menus
GROUP BY shop_name
HAVING MAX(price) <= 500; ●極値関数とNULL ・MIN、MAXは引数からNULLを除外 ・ALLではNULLが含まれる ●COUNT(*)とCOUNT(列名)の違い ・COUNT(*) : 全行を数える ・COUNT(列名) : ある列からNULLを除外した行を数える #全称量化