今回はPostgreSQLの条件指定を復習し、AND・OR・NOT・IN・BETWEEN・LIKEを組み合わせます。取得したデータの意味を整理します。
所要時間は15分ほどです。
それでは、さっそく始めていきましょう！
今日のテーマと到達目標
条件を組み合わせたSQLを書き、取得対象になる行を説明できることが目標です。
| 指定 | 意味 |
|---|---|
| AND | 両方の条件を満たす |
| OR | 少なくとも一方の条件を満たす |
| NOT | 条件を否定する |
| IN | 候補のいずれかと一致する |
| BETWEEN | 両端を含む範囲に入る |
| LIKE | 文字列のパターンに一致する |
今回は値が入力済みの都市と開業日を扱います。取得元はmobility.poisで、1行は1施設です。poi_idは施設ID、poi_nameは施設名、cityは都市、opened_dateは開業日です。
演習①：都市と開業期間の両方を指定する
TokyoまたはShinjukuにあり、2021年1月1日から2023年12月31日までに開業した施設を取得しました。
SELECT poi_id, poi_name, city, opened_date
FROM mobility.pois
WHERE city IN ('Tokyo', 'Shinjuku')
AND opened_date BETWEEN DATE '2021-01-01' AND DATE '2023-12-31'
ORDER BY poi_id;
自分でSQLを書いて実行した結果は7行でした。
| poi_id | city | opened_date |
|---|---|---|
| 1 | Tokyo | 2021-01-15 |
| 17 | Tokyo | 2021-02-09 |
| 18 | Shinjuku | 2023-04-24 |
| 26 | Shinjuku | 2021-02-11 |
| 33 | Tokyo | 2021-09-07 |
| 50 | Shinjuku | 2023-03-25 |
| 57 | Tokyo | 2021-07-16 |
「Tokyoにあっても2024年開業なら含まれるか」という質問には、「含まれません」と回答しました。都市条件は満たしても、期間条件を満たさないためです。
このSQLの最初のANDは都市条件と期間条件をつなぎ、BETWEEN内のANDは下限と上限を区切っています。
演習②：複数条件の全体を否定する
次の課題は「都市名がShiで始まる、またはTokyoである」という条件全体をNOTで否定し、施設ID順に先頭5行を取得することでした。
最初は次のSQLを実行しました。
SELECT poi_id, poi_name, city
FROM mobility.pois
WHERE NOT city LIKE 'Shi%' OR city = 'Tokyo'
ORDER BY poi_id ASC;
構文エラーにはなりませんでしたが、結果はTokyoを含む48行でした。否定する範囲を示す括弧と、LIMIT 5が抜けていました。
今回の最初の条件は、次の意味になります。
(NOT (city LIKE 'Shi%')) OR city = 'Tokyo'
これは「Shiで始まらない、またはTokyoである」という条件です。ORでつないだ条件全体を否定したものではありません。
括弧とLIMITを修正する
説明を受けて、次のように修正しました。
SELECT poi_id, poi_name, city
FROM mobility.pois
WHERE NOT (city LIKE 'Shi%' OR city = 'Tokyo')
ORDER BY poi_id ASC
LIMIT 5;
実行結果は次の5行でした。
| poi_id | poi_name | city |
|---|---|---|
| 4 | Ikebukuro_cafe_004 | Ikebukuro |
| 5 | Yokohama_office_005 | Yokohama |
| 6 | Kawasaki_park_006 | Kawasaki |
| 7 | Omiya_hotel_007 | Omiya |
| 8 | Kichijoji_entertainment_008 | Kichijoji |
条件に合う施設のうち先頭5件を表示したもので、対象の総数が5件という意味ではありません。
NOTを付ける前と後を区別する
修正後の条件をShibuyaとYokohamaが満たすかという質問で、最初は「Shibuyaは満たす、Yokohamaは満たさない」と逆に回答しました。
括弧の内側を判定した後に、NOTで反転させる必要があります。
| 都市 | Shiで始まる、またはTokyoである | 全体をNOTで否定した条件 |
|---|---|---|
| Shibuya | 成り立つ | 満たさない |
| Yokohama | 成り立たない | 満たす |
最初の回答はNOTを付ける前の判定に対応していました。
その後、別の都市で再確認しました。「Ikebukuroは満たすか」という質問には、「満たす。Shiで始まらず、Tokyoでもないから」という趣旨で回答できました。
今回の値が入力済みのcityでは、修正後の条件は次のようにも整理できます。
Shiで始まらない
かつ
Tokyoではない
この説明は条件の意味を整理したもので、別のSQLを追加実行した結果ではありません。
合格条件と学習記録
| 確認項目 | 確認できた内容 |
|---|---|
| IN・BETWEEN・ANDを組み合わせられる | 都市と期間の両条件を満たす7施設を取得 |
| 期間外の施設を判定できる | Tokyoでも2024年開業なら含まれないと回答 |
| NOTの範囲を修正できる | 括弧とLIMITを追加し、課題どおりの5行を取得 |
| 否定後の条件を説明できる | 誤答の復習後、Ikebukuroが満たす理由を回答 |
今回は支援を受けて修正した演習もありました。特にNOTの対象範囲と、否定する前後の判定を分けることが復習点です。
まとめ
SQLが実行できても、意図した条件になっているとは限りません。複数条件を組み合わせるときは、括弧の範囲、条件全体の意味、表示件数の指定を確認します。
NOTがある場合は、まず否定対象の条件を判定し、その結果を反転させて考えます。