SQL ServerのWHERE句で抽出結果を照合する。番号の入れ替わりと0件の原因

SQL ServerのWHERE句の練習結果。括弧なしは4件、括弧ありは3件、そこからメモ未入力に絞ると1件。

SQL Serverで備品依頼を絞ると、同じ4件でも、控室の15番が外れて手配済みの12番が入ることがあります。WHEREは条件に合う行を選ぶ指定です。件数と依頼番号を照合し、「受付か会場で未手配」の行を探すときは、二つの場所を括弧でまとめます。

最後の⑤だけを差し替えると、数量が範囲に入らない0件と、値が入っていない状態を表すNULLを等号で比べた0件を見分けられます。元の条件と差し替え後で、どの表を見ればよいかも分かります。

練習には、接続を許可されたSQL Server 2022と、SQLを入力・実行するアプリのSQL Server Management Studio(SSMS)を使います。データは架空の備品依頼6件です。

先に結論

  • ②と③はどちらも4件ですが、②の15番が③では12番に入れ替わります。件数が合うときも、依頼番号を1件ずつ照合します。
  • 元の⑤は最初の比較表の11番と照合します。条件を差し替えた⑤は、差し替え表で選んだ行の番号と照合します。
  • 数量の範囲に該当する行がない場合も、NULLを等号で比べた場合も⑤は0件です。①の元データと、⑤で指定した条件の両方を見て原因を判断します。

SSMSで練習用のクエリを開く

練習先は、自分のPC上か、練習用として案内されたSQL Serverです。接続に使うサーバー名と認証方法を確認します。認証は、接続する利用者を確認する仕組みです。

掲載結果の取得環境は、ローカルのSQL Server 2022(16.00.1000)です。クエリ欄は、データベースへの指示をSQLで入力する場所です。練習の6件は、次の節のSQLを実行すると用意されます。

  1. SSMSの接続画面で練習用のサーバー名を指定し、許可された認証方法で接続します。
  2. 画面左の[オブジェクト エクスプローラー]で接続先の名前を確認し、その接続から[新しいクエリ]を開きます。
  3. 画面上部のデータベース一覧でtempdbを選びます。tempdbはSQL Serverが一時データに使うデータベースです。接続先や一覧が分からない場合は、案内された練習先を確認してから進みます。

掲載SQLを全文実行して五つの結果を出す

クエリ欄に掲載例を全文貼り付け、文字の選択を解除してから実行します。

DECLAREで、備品依頼を一時的に置く表変数を用意します。表変数を使えるのは、宣言したバッチの中です。バッチはSQL Serverへまとめて送るSQLのひとまとまりです。INSERT INTOとVALUESで、この表変数に6件を入れます。

intは整数、nvarcharは日本語などの文字列を入れられる型です。型は列に入る値の種類を表します。文字列は一重引用符で囲み、その前のNは日本語などを扱える形式で指定するための印です。セミコロンはSQLの文を区切ります。

SELECTは表示する列、FROMは読み出す表、ORDER BYは表示順を指定します。この例では依頼番号の小さい順に並びます。練習用のクエリ欄に下のSQLを貼り付け、選択範囲がない状態で[実行]を選ぶか、F5キーを押します。

備品依頼6件を5通りで表示する練習用SQL

DECLARE @requests TABLE (
    request_no int, area_name nvarchar(8),
    request_status nvarchar(8), quantity int, note nvarchar(20) NULL
);
INSERT INTO @requests (request_no, area_name, request_status, quantity, note)
VALUES
    (11, N'受付', N'未手配', 3, NULL),
    (12, N'受付', N'手配済', 1, N'確認済み'),
    (13, N'会場', N'未手配', 2, N''),
    (14, N'会場', N'手配済', 5, NULL),
    (15, N'控室', N'未手配', 2, NULL),
    (16, N'会場', N'未手配', 4, N'折り返し');

-- ① 元の6行
SELECT request_no, area_name, request_status, quantity, note
FROM @requests ORDER BY request_no;

-- ② 状態が未手配の行
SELECT request_no FROM @requests
WHERE request_status = N'未手配'
ORDER BY request_no;

-- ③ 括弧を付けずに比較
SELECT request_no FROM @requests
WHERE area_name = N'受付' OR area_name = N'会場'
  AND request_status = N'未手配'
ORDER BY request_no;

-- ④ 受付または会場のうち、未手配の行
SELECT request_no FROM @requests
WHERE (area_name = N'受付' OR area_name = N'会場')
  AND request_status = N'未手配'
ORDER BY request_no;

-- ⑤ ④のうち、メモが未入力の行
SELECT request_no FROM @requests
WHERE (area_name = N'受付' OR area_name = N'会場')
  AND request_status = N'未手配'
  AND note IS NULL
ORDER BY request_no;

実行結果

1. request_no(6件)  11, 12, 13, 14, 15, 16
2. request_no(4件)  11, 13, 15, 16
3. request_no(4件)  11, 12, 13, 16
4. request_no(3件)  11, 13, 16
5. request_no(1件)  11

画面下部の[結果]欄に上から五つの表が並び、掲載例の①〜⑤に対応します。request_no列は依頼番号です。①には11〜16番の6件と場所、状態、数量、メモが表示され、②〜⑤には条件に合った依頼番号が表示されます。

同じ4件の②と③で入れ替わる番号を追う

②と③の共通の番号は11・13・16番です。残る1件は②が15番、③が12番です。

②は等号で状態の列と「未手配」が一致する行を選びます。場所を問わないため、控室で未手配の15番も残ります。③はANDとORを使って場所と状態を組み合わせます。ANDは両方、ORは少なくとも一方の条件が成立するときに行を選ぶ指定です。

括弧がない③では、ANDがORより強く結び付くため、「会場」と「未手配」が一組になります。③に入るのは、受付の全件と、会場で未手配の依頼です。15番は控室なので外れ、12番は受付なので手配済みでも入ります。

④では「受付または会場」を括弧で一組にし、未手配の条件をANDでつなぎます。受付と会場の両方に未手配の条件がかかり、12番が外れます。括弧が決めるのは条件の組み合わせです。各条件を実際に調べる順序はSQL Serverが決めます。

目的 結果の位置と条件 request_no
全6件 ① 条件なし 11・12・13・14・15・16
未手配 ② 状態のみ 11・13・15・16
括弧なしの比較 ③ 受付、または会場の未手配 11・12・13・16
受付か会場で未手配 ④ 場所を括弧でまとめる 11・13・16
④でメモ未入力 ⑤ IS NULLを追加 11

下の比較表と[結果]欄を照合します。②と③では依頼番号の入れ替わりを見て、③と④では12番だけが外れ、11・13・16番が残ることを確認します。この表は、掲載SQLを元の条件で実行したときの照合に使います。

元の⑤で空欄に見える13番が外れる理由

①のnote列はメモです。11番は値が入っていないNULL、13番は0文字の値が入っている空文字です。13番のメモは、一重引用符の間を空にして指定しています。結果欄で空欄に見えたら、①の依頼番号と一緒に確認します。

元の⑤は、④で残った11・13・16番から、IS NULLでメモに値が入っていない行を選びます。該当するのは11番です。13番には空文字、16番には「折り返し」が入っているため、どちらもIS NOT NULLに当てはまります。

①では14番と15番のメモもNULLです。14番は手配済み、15番は控室で、どちらも④の場所と状態の条件で外れています。メモだけで絞った場合の番号は、次の節の差し替え表で確認できます。

⑤だけを差し替えて照合する表を切り替える

差し替え後は①〜④が元の結果と一致し、⑤が選んだ条件の行と一致すれば成功です。

最初は差し替え表の「数量が2〜4」を使います。BETWEENは下限と上限も含めて範囲を指定します。①のquantity列は数量です。2個の13・15番、3個の11番、4個の16番が該当します。

INは括弧に並べた値のどれかと一致する行を探します。LIKEは文字の並び方を指定します。パーセント記号は0文字以上を表すため、「確認」の後ろに付けると、「確認」で始まるメモを探せます。

  1. 掲載例を実行した練習用タブで、コメント「⑤ ④のうち、メモが未入力の行」の下にあるWHEREを探します。
  2. ⑤のWHEREの後ろからORDER BYの直前までの条件全体を、この節の差し替え表の中央列から選んだ一行に置き換えます。WHEREとORDER BYは残します。
  3. クエリ欄の文字の選択を解除して全文を実行します。①〜④は最初の比較表、⑤は差し替え表で選んだ行の右端と照合します。別の条件を試すときも、同じ箇所を一つの条件に置き換えます。
調べたい内容 置き換える条件 ⑤の番号
数量が2〜4 quantity BETWEEN 2 AND 4 11・13・15・16
受付か会場 area_name IN (N'受付', N'会場') 11・12・13・14・16
確認から始まるメモ note LIKE N'確認%' 12
メモがNULL note IS NULL 11・14・15
値が入っているメモ note IS NOT NULL 12・13・16
NULLを等号で比較した例 note = NULL 0件
数量が7〜9 quantity BETWEEN 7 AND 9 0件

「数量が2〜4」では、⑤に11・13・15・16番が出ます。「メモがNULL」だけに差し替えると、場所と状態の条件が外れるため11・14・15番が出ます。表の右端にある番号と、⑤のrequest_no列を上から照合します。

範囲外の0件とNULLの等号比較による0件を見分ける

最後の結果が空なら、①の数量やメモと、⑤に入れた条件を見比べます。

前の節と同じ練習用タブで、⑤だけを差し替えます。WHEREが選ぶのは条件が成立した行です。NULLを等号で比べると、一致するかどうかの判定が不明になります。判定が不明の行は結果から外れます。

  1. 前の節の差し替え表で「数量が7〜9」の中央列を選び、⑤の条件全体を置き換えます。選択を解除して全文を実行し、①〜④が元の結果と一致して⑤が0件になることを確認します。
  2. 同じ位置を、差し替え表の「NULLを等号で比較した例」の中央列に置き換えます。選択を解除して全文を実行し、①〜④が元の結果と一致して⑤が0件になることを確認します。
  3. ⑤を「メモがNULL」の条件に差し替え、もう一度全文を実行します。⑤の11・14・15番を、①のメモ欄と照合します。

数量の例では①のquantity列に7〜9の値がありません。等号の例では①のnote列にNULLがあり、メモがNULLという条件で3件を取り出せます。⑤が同じ0件でも、どの条件を指定したかで原因を見分けられます。

結果がずれたときの確認先と元に戻す手順

まず[結果]欄に五つの表が出ているか、①に11〜16番の6件がそろっているかを確認します。⑤の番号が予想と違う場合は、いま実行したのが元の掲載SQLか、⑤を差し替えたSQLかをクエリ欄で確かめます。

  • 表が足りないときは[メッセージ]欄を確認します。変数の宣言を求めるエラーが出た場合は、文字の選択を解除し、DECLAREから最後のORDER BYまで全文を再実行します。
  • 元の掲載SQLで③や④がずれたときは、場所の条件を囲む括弧を確認します。⑤だけがずれたときは、最初の比較表を開き、④の場所と状態の条件にメモのIS NULLが加わっているか確認します。
  • ⑤を差し替えた後は、差し替え表で選んだ行の中央列と現在の⑤の条件を見比べ、右端の番号と結果を照合します。①〜④にも差があれば、掲載SQLを全文貼り直してから⑤だけを差し替えます。

元に戻すには、同じ練習用タブの内容を掲載SQLの全文で置き換え、選択を解除して実行します。最初の比較表と五つの結果がそろい、⑤が11番の1件に戻れば復元できています。

練習データはバッチの終了時に自動で片付きます。照合が済んだら練習用タブを閉じます。実務で条件を確かめるときは、参照を許可された環境でSELECTを使い、対象の番号と件数を先に確認します。