一覧DB-02-2 · SELECTの基本

文字列の検索とNULLの扱い

商品名とカテゴリを持つ六行を用意します。LIKEの記号、INとNOT IN、IS NULLを順に使い、比較式が返す1・0・NULLと、WHEREで選ばれる行を対応させます。最後に括弧によるAND/ORの条件範囲の違いを確認します。

説明

検索条件には、文字列の一部分に一致するもの、候補のどれかと一致するもの、値が未登録かを調べるものがあります。特にNULLを含む比較は、真・偽だけでは説明できません。

商品名とカテゴリを持つ六行を用意します。LIKEの記号、INとNOT IN、IS NULLを順に使い、比較式が返す1・0・NULLと、WHEREで選ばれる行を対応させます。最後に括弧によるAND/ORの条件範囲の違いを確認します。

1検索する商品名とカテゴリ

%と_を実際に含む名前も用意します。

CREATE TABLE items三列の商品表(id INTEGER PRIMARY KEY,name TEXT,category TEXT) STRICT; INSERT INTO items VALUES六行の試料(1,'青ペン','文具'),(2,'赤ペン','文具'),(3,'100%ジュース','飲料'),(4,'100点ノート','文具'),(5,'A_メモ',NULL),(6,'ABメモ','文具'); SELECT * FROM items検索前の全行 ORDER BY id;

id 3の名前には%が、id 5には_が入っています。カテゴリが未登録なのはid 5だけです。これらを使って、パターン中の記号と普通の文字の違いを確かめます。

2名前がペンで終わる商品

LIKEの先頭へ%を付けます。

SELECT id,name FROM items WHERE name LIKE '%ペン'末尾がペンの名前 ORDER BY id;

%は0文字以上の任意の文字列です。青ペンと赤ペンが一致します。LIKEはパターン全体で照合するので、末尾に%を付けなければペンの後に別の文字が続く名前は対象になりません。

3%を記号として検索するとき

100に続く任意の文字列を検索します。

SELECT id,name FROM items WHERE name LIKE '100%'100から始まる名前 ORDER BY id;

100%ジュースと100点ノートが一致します。パターンの%は「%という一文字」を意味していません。文字100から始まる名前を選んでいます。

4文字としての%を指定する

!をエスケープ文字として宣言します。

SELECT id,name FROM items WHERE name LIKE '100!%%'100と文字の%、その後は任意 ESCAPE '!'!をエスケープ文字にする ORDER BY id;

100%ジュースだけになります。!%は文字としての%、その後の%は任意の文字列です。ESCAPEで指定した一文字の直後は、ワイルドカードを文字として扱えます。

5_は任意の一文字

Aの次に一文字、その後がメモという名前を検索します。

SELECT id,name FROM items WHERE name LIKE 'A_メモ'Aとメモの間が一文字 ORDER BY id;

A_メモとABメモの両方です。_はちょうど一文字に一致し、その文字自体が_でも構いません。任意長の%とは長さの条件が違います。

6文字としての_と大文字小文字

_をエスケープして一行に絞り、LIKEの文字比較も確認します。

SELECT id,name FROM items WHERE name LIKE 'A!_メモ'_を普通の文字として照合 ESCAPE '!' ORDER BY id; SELECT 'a' LIKE 'A'ASCIIの大小文字 AS ascii_match,'æ' LIKE 'Æ'ASCII外の文字 AS non_ascii_match;

名前はA_メモだけです。既定のSQLiteのLIKEではASCIIのa/Aは一致しますが、æ/Æは一致しません。すべての言語の大文字小文字を同じ規則で比較する機能ではありません。

7候補のどれかに一致するID

INで1・3・5のいずれかを指定します。

SELECT id,name FROM items WHERE id IN (1,3,5)三つの候補のいずれか ORDER BY id;

青ペン、100%ジュース、A_メモです。この非NULLのIDでは、id=1 OR id=3 OR id=5と同じ対象になります。INは候補の一覧を条件として表します。

8NULLと等しいという比較

同じ列に対して= NULLとIS NULLの結果を並べます。

SELECT id,category,category=NULL通常の等号で比較 AS eq_null,category IS NULLNULLかを判定 AS is_null FROM items ORDER BY id;

eq_nullは全行NULLです。NULLは値が不明・存在しない状態であり、等号で一致を確定できません。IS NULLなら未登録のid 5だけが1、他の行は0になります。

9未登録の行を取り出す

IS NULLをWHEREへ指定します。

SELECT id,name FROM items WHERE category IS NULL未登録のカテゴリ ORDER BY id;

id 5だけです。WHEREは条件が真の行を残し、偽やNULLの行を除きます。そのためWHERE category=NULLでは目的の行を選べません。

10NOT INにNULLを混ぜる

除外候補に文具とNULLを指定し、式の値を表示します。

SELECT id,category,category NOT IN ('文具',NULL)NULLを含む除外候補 AS keep FROM items ORDER BY id;

文具の行は0、飲料と未登録の行はNULLです。飲料は文具とは違いますが、もう一つの候補NULLと一致するかを確定できません。NOT INを「列挙した値以外」と考えるだけでは、NULLを含む結果を説明できません。

11未登録も含めて文具以外を取得する

文具ではない行と、カテゴリ未登録の行を明示的に指定します。

SELECT id,name,category FROM items WHERE category<>'文具'値が文具ではない OR category IS NULL未登録も含める ORDER BY id;

飲料のid 3と未登録のid 5が残ります。<>は等しくない値の比較です。NULLには比較結果がNULLになるため、未登録を含める要件をOR category IS NULLとして書き足しています。

12ANDとORの範囲を括弧で示す

同じ条件を括弧の有無で実行します。

SELECT id FROM items WHERE category='文具' OR category IS NULL AND id>=5ANDが未登録の条件に結び付く ORDER BY id; SELECT id FROM items WHERE (category='文具' OR category IS NULL) AND id>=5括弧内全体にIDの条件を掛ける ORDER BY id;

括弧なしでは1・2・4・5・6、括弧ありでは5・6です。ANDはORより先に結び付きます。前者でid>=5が掛かるのは未登録の側だけ、後者では文具・未登録の両方へ掛かります。

まとめ

LIKEの%は任意長、_は一文字に一致し、文字として検索する場合はESCAPEを指定します。

WHEREは真の行だけを残し、NULLはIS NULLで判定します。

NOT INのNULLとAND/ORの結び付きは、式の値を表示して確認できます。

操作履歴と解説を振り返れます。

SQLite 3.54.0

1
CREATE TABLE items(id INTEGER PRIMARY KEY,name TEXT,category TEXT) STRICT; INSERT INTO items VALUES(1,'青ペン','文具'),(2,'赤ペン','文具'),(3,'100%ジュース','飲料'),(4,'100点ノート','文具'),(5,'A_メモ',NULL),(6,'ABメモ','文具'); SELECT * FROM items ORDER BY id;
╭────┬──────────────┬──────────╮
│ id │     name     │ category │
╞════╪══════════════╪══════════╡
│  1 │ 青ペン       │ 文具     │
│  2 │ 赤ペン       │ 文具     │
│  3 │ 100%ジュース │ 飲料     │
│  4 │ 100点ノート  │ 文具     │
│  5 │ A_メモ       │ NULL     │
│  6 │ ABメモ       │ 文具     │
╰────┴──────────────┴──────────╯
2
SELECT id,name FROM items WHERE name LIKE '%ペン' ORDER BY id;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  1 │ 青ペン │
│  2 │ 赤ペン │
╰────┴────────╯
3
SELECT id,name FROM items WHERE name LIKE '100%' ORDER BY id;
╭────┬──────────────╮
│ id │     name     │
╞════╪══════════════╡
│  3 │ 100%ジュース │
│  4 │ 100点ノート  │
╰────┴──────────────╯
4
SELECT id,name FROM items WHERE name LIKE '100!%%' ESCAPE '!' ORDER BY id;
╭────┬──────────────╮
│ id │     name     │
╞════╪══════════════╡
│  3 │ 100%ジュース │
╰────┴──────────────╯
5
SELECT id,name FROM items WHERE name LIKE 'A_メモ' ORDER BY id;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  5 │ A_メモ │
│  6 │ ABメモ │
╰────┴────────╯
6
SELECT id,name FROM items WHERE name LIKE 'A!_メモ' ESCAPE '!' ORDER BY id; SELECT 'a' LIKE 'A' AS ascii_match,'æ' LIKE 'Æ' AS non_ascii_match;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  5 │ A_メモ │
╰────┴────────╯
╭─────────────┬─────────────────╮
│ ascii_match │ non_ascii_match │
╞═════════════╪═════════════════╡
│           1 │               0 │
╰─────────────┴─────────────────╯
7
SELECT id,name FROM items WHERE id IN (1,3,5) ORDER BY id;
╭────┬──────────────╮
│ id │     name     │
╞════╪══════════════╡
│  1 │ 青ペン       │
│  3 │ 100%ジュース │
│  5 │ A_メモ       │
╰────┴──────────────╯
8
SELECT id,category,category=NULL AS eq_null,category IS NULL AS is_null FROM items ORDER BY id;
╭────┬──────────┬─────────┬─────────╮
│ id │ category │ eq_null │ is_null │
╞════╪══════════╪═════════╪═════════╡
│  1 │ 文具     │ NULL    │       0 │
│  2 │ 文具     │ NULL    │       0 │
│  3 │ 飲料     │ NULL    │       0 │
│  4 │ 文具     │ NULL    │       0 │
│  5 │ NULL     │ NULL    │       1 │
│  6 │ 文具     │ NULL    │       0 │
╰────┴──────────┴─────────┴─────────╯
9
SELECT id,name FROM items WHERE category IS NULL ORDER BY id;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  5 │ A_メモ │
╰────┴────────╯
10
SELECT id,category,category NOT IN ('文具',NULL) AS keep FROM items ORDER BY id;
╭────┬──────────┬──────╮
│ id │ category │ keep │
╞════╪══════════╪══════╡
│  1 │ 文具     │    0 │
│  2 │ 文具     │    0 │
│  3 │ 飲料     │ NULL │
│  4 │ 文具     │    0 │
│  5 │ NULL     │ NULL │
│  6 │ 文具     │    0 │
╰────┴──────────┴──────╯
11
SELECT id,name,category FROM items WHERE category<>'文具' OR category IS NULL ORDER BY id;
╭────┬──────────────┬──────────╮
│ id │     name     │ category │
╞════╪══════════════╪══════════╡
│  3 │ 100%ジュース │ 飲料     │
│  5 │ A_メモ       │ NULL     │
╰────┴──────────────┴──────────╯
12
SELECT id FROM items WHERE category='文具' OR category IS NULL AND id>=5 ORDER BY id; SELECT id FROM items WHERE (category='文具' OR category IS NULL) AND id>=5 ORDER BY id;
╭────╮
│ id │
╞════╡
│  1 │
│  2 │
│  4 │
│  5 │
│  6 │
╰────╯
╭────╮
│ id │
╞════╡
│  5 │
│  6 │
╰────╯

演習完了