点検予定と点検記録を照合すると、記録がまだない予定が一覧から消れることがあります。その予定も残すには、予定表を左に置いてLEFT JOINを使います。SQLはデータベースに指示を出す言語で、JOINは条件に沿って2つの表の行を組み合わせる操作です。
練習には、接続できるローカルのSQL Server 2022と、SQLを入力して結果を見るSQL Server Management Studio(SSMS)を使います。文化祭の備品点検を例に、4種類の結合でどの行が残るか、合格条件を置く場所で結果がどう変わるかを読み取ります。
先に結論
- 条件に合う組だけを取り出すにはINNER JOINを使います。相手のいない行も残す場合は、左の表ならLEFT JOIN、右の表ならRIGHT JOIN、両方の表ならFULL OUTER JOINを使います。
- 練習の結果表では、先頭列StepにLEFT JOINなら「2 LEFT」、RIGHT JOINなら「3 RIGHT」と表示されます。どちらも4行なので、PlanNo(予定表の予定番号)とLogNo(記録番号)で残った行を見分けます。
- ONには結合相手を選ぶ条件を書き、WHEREには結合後の行を絞る条件を書きます。全予定を残し、合格記録があれば添えるには、合格条件をONに置きます。
SSMSで練習用のタブを開く
SSMSでSQLを入力して実行する画面をクエリタブと呼びます。ここでは点検予定3件と点検記録4件を使います。ローカルの練習用サーバーに接続してクエリタブを開き、結果を行と列のある表で読めるようにします。
- SSMSを開き、ローカルの練習用SQL Serverに接続します。
- 接続したサーバーを選び、[新しいクエリ]を開きます。タブの下部に練習用サーバーの名前が表示されていることを確認します。
- Ctrl+Dを押し、実行結果を表で表示する形式に切り替えます。
SQLを貼り付けて6つの結果表を出す
掲載コードをまとめて実行し、結果表の先頭列Stepを目印に出力例と照合します。
先頭のDECLAREは、ひとまとまりの実行中だけ使う練習用の表を用意します。この表をテーブル変数と呼びます。INSERT INTOで行を追加します。記録にあるCheckStateは点検結果を表し、OKは合格、HOLDは保留です。
番号に使うintは整数を入れる型です。varchar(4)は文字列を入れる型で、この例の半角英字なら4文字まで入ります。PRIMARY KEYを指定した列は表の中で行ごとに異なる値を持ち、NOT NULLを指定した列には値が必要です。
FROMの直後の予定表が左、JOINの直後の記録表が右です。ASで付けた別名のうち、pは予定表、l(小文字のエル)は記録表を指します。p.PlanNoは「予定表pのPlanNo列」です。ピリオドの左側が表の別名、右側が列名です。ONで両表の予定番号を比べます。
SELECTは結果に表示する列を指定します。LogPlanNoには、記録に書かれた予定番号が表示されます。ORDER BYは結果の行を並べる順序を指定します。最後に出てくるORDER BYを、コピー範囲の終わりを探す目印にします。
- 掲載コードの先頭のDECLAREから、最後のORDER BYの末尾にあるセミコロンまでをコピーし、練習用のクエリタブに貼り付けます。コードに続く出力例は、実行後の照合に使います。
- 入力欄で文字の選択を解除してF5を押します。F5は文字を選択している場合は選んだ部分だけを、選択を解除した場合は入力欄の全文を実行します。
点検予定3件と点検記録4件を全文で実行する
DECLARE @Plan TABLE (PlanNo int PRIMARY KEY);
DECLARE @Log TABLE (
LogNo int PRIMARY KEY,
PlanNo int NOT NULL,
CheckState varchar(4) NOT NULL
);
INSERT INTO @Plan (PlanNo) VALUES (410), (420), (430);
INSERT INTO @Log (LogNo, PlanNo, CheckState)
VALUES (8101, 410, 'OK'), (8102, 410, 'HOLD'),
(8103, 420, 'HOLD'), (8104, 490, 'OK');
SELECT '1 INNER' AS Step, p.PlanNo, l.LogNo, l.PlanNo AS LogPlanNo
FROM @Plan AS p
INNER JOIN @Log AS l ON p.PlanNo = l.PlanNo
ORDER BY p.PlanNo, l.LogNo;
SELECT '2 LEFT' AS Step, p.PlanNo, l.LogNo, l.PlanNo AS LogPlanNo
FROM @Plan AS p
LEFT JOIN @Log AS l ON p.PlanNo = l.PlanNo
ORDER BY p.PlanNo, l.LogNo;
SELECT '3 RIGHT' AS Step, p.PlanNo, l.LogNo, l.PlanNo AS LogPlanNo
FROM @Plan AS p
RIGHT JOIN @Log AS l ON p.PlanNo = l.PlanNo
ORDER BY l.PlanNo, l.LogNo;
SELECT '4 FULL' AS Step, p.PlanNo, l.LogNo, l.PlanNo AS LogPlanNo
FROM @Plan AS p
FULL OUTER JOIN @Log AS l ON p.PlanNo = l.PlanNo
ORDER BY COALESCE(p.PlanNo, l.PlanNo), l.LogNo;
SELECT '5 ON' AS Step, p.PlanNo, l.LogNo, l.PlanNo AS LogPlanNo
FROM @Plan AS p
LEFT JOIN @Log AS l ON p.PlanNo = l.PlanNo
AND l.CheckState = 'OK'
ORDER BY p.PlanNo, l.LogNo;
SELECT '6 WHERE' AS Step, p.PlanNo, l.LogNo, l.PlanNo AS LogPlanNo
FROM @Plan AS p
LEFT JOIN @Log AS l ON p.PlanNo = l.PlanNo
WHERE l.CheckState = 'OK'
ORDER BY p.PlanNo, l.LogNo;
実行結果
Step | PlanNo | LogNo | LogPlanNo
---------|--------|-------|----------
1 INNER | 410 | 8101 | 410
1 INNER | 410 | 8102 | 410
1 INNER | 420 | 8103 | 420
2 LEFT | 410 | 8101 | 410
2 LEFT | 410 | 8102 | 410
2 LEFT | 420 | 8103 | 420
2 LEFT | 430 | NULL | NULL
3 RIGHT | 410 | 8101 | 410
3 RIGHT | 410 | 8102 | 410
3 RIGHT | 420 | 8103 | 420
3 RIGHT | NULL | 8104 | 490
4 FULL | 410 | 8101 | 410
4 FULL | 410 | 8102 | 410
4 FULL | 420 | 8103 | 420
4 FULL | 430 | NULL | NULL
4 FULL | NULL | 8104 | 490
5 ON | 410 | 8101 | 410
5 ON | 420 | NULL | NULL
5 ON | 430 | NULL | NULL
6 WHERE | 410 | 8101 | 410
SSMSの結果欄には6つの表が並びます。掲載の出力例では、6つの表を1つにまとめています。先頭列Stepの値が変わるところで区切り、1 INNER、2 LEFT、3 RIGHT、4 FULL、5 ON、6 WHEREの順に照合します。
NULLは値がないことを表します。この練習では入力した番号に値があるため、結果の予定番号や記録番号がNULLなら、その側に結合条件を満たす相手の行が付かなかったと分かります。
LEFTとRIGHTの4行を番号で見分ける
1 INNERでは、予定410に記録8101と8102が付き、予定420に記録8103が付きます。一つの予定に複数の記録が対応すると、組ごとに1行が出ます。予定410が2行に並ぶのはこのためです。
比較表の「片側だけにある行」は、相手が見つからず片方の表の行だけが残ったものです。2 LEFTと3 RIGHTでは、一致した3組に加えて、どの番号が残るかを比べます。
| 結果表 | 行数 | 片側だけにある行 |
|---|---|---|
| 1 INNER | 3行 | なし |
| 2 LEFT | 4行 | 予定430 |
| 3 RIGHT | 4行 | 記録8104(予定番号490) |
| 4 FULL | 5行 | 予定430と記録8104(予定番号490) |
2 LEFTでは予定430が残り、LogNoはNULLです。3 RIGHTでは記録8104が残り、PlanNoはNULLです。記録8104のLogPlanNoは490なので、記録には予定490と書かれていたと分かります。4 FULLでは予定430と記録8104の両方が残ります。
OUTERは、相手が見つからない行も指定した側に残す結合を表します。LEFT、RIGHT、FULLではOUTERを省略できます。
4 FULLの並べ替えに使うCOALESCEは、指定した項目を左から順に見て、最初に値があるものを返します。記録8104の行では予定表側の番号がNULLなので、記録に書かれた予定番号490が並べ替えに使われます。
合格条件の置き場所で残る予定が変わる
この例では、5 ONは予定を3行残し、6 WHEREは合格した組の1行を残します。
5 ONでは、予定番号が同じことと、点検結果がOKであることをANDでつなぎます。ANDは両方の条件を満たすという意味です。予定410には合格の記録8101が付きます。予定420と430は記録が付かない状態で残ります。
6 WHEREでは、予定番号で組を作ってから、点検結果がOKの行に絞ります。点検結果がNULLの場合、OKに等しいという判定は真になりません。残るのは予定410と記録8101の組です。
予定420と430に合格記録が付かなかった理由は、合格条件を加える前の2 LEFTと、掲載コードの入力値を順に見ると分かります。
- 5 ONで、予定420と430のLogNoがどちらもNULLであることを確認します。
- 2 LEFTで同じ予定番号を探します。予定420のLogNoは8103です。予定430のLogNoはここでもNULLです。
- 掲載コードの冒頭にある2つ目のINSERT INTOで、1番目の値が記録番号8103の組を探します。3番目の値はCheckStateで、HOLD(保留)と読めます。
予定420には保留の記録8103があり、予定430に対応する記録は0件です。5 ONではどちらも予定が残ります。6 WHEREではどちらも合格条件を通らず、一覧から外れます。
結果表が6つそろわないときは全文を実行し直す
実行中に止める必要がある場合は、練習用タブで[クエリの実行を取り消す]を選ぶか、Alt+Breakを押します。結果表が6つそろわない場合は、同じタブのメッセージ欄を読みます。表の宣言を求めるエラーが出たら、先頭のDECLAREを含めて実行したか確認します。
- 同じ練習用タブで、先頭のDECLAREから最後のORDER BYの末尾までが入力欄にあることを確認します。貼り直す場合は、[SQLを貼り付けて6つの結果表を出す]の手順1の範囲で入力欄を置き換えます。
- 文字の選択を解除してF5を押します。結果表が6つそろい、最初の1 INNERが3行、最後の6 WHEREが予定410と記録8101の1行であることを確認します。
- 練習が終わったらタブを閉じます。練習用の表は実行が終わると自動で片付きます。
掲載結果の取得環境は、ローカルのSQL Server 2022(16.00.1000)のtempdbです。接続に.NET SqlClientを使い、同じ接続で全文を2回実行した記録では、6つの表の順序、列名、全行が一致しています。SSMSの操作案内は公式資料に基づきます。
実際の台帳に応用するときは、予定を数えるのか、予定と記録の組を数えるのかを先に決めます。この練習では、入力した予定が3件でも、2 LEFTの結果は4行になります。
