SQL ServerのJOIN入門。4種類の結合で残る行を見分ける

点検台帳の結合を比較する図。LEFTは相手のいない予定430、RIGHTは相手のいない記録8104、FULLはその両方を残します。

点検予定と点検記録を照合すると、記録がまだない予定が一覧から消れることがあります。その予定も残すには、予定表を左に置いて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件を使います。ローカルの練習用サーバーに接続してクエリタブを開き、結果を行と列のある表で読めるようにします。

  1. SSMSを開き、ローカルの練習用SQL Serverに接続します。
  2. 接続したサーバーを選び、[新しいクエリ]を開きます。タブの下部に練習用サーバーの名前が表示されていることを確認します。
  3. 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を、コピー範囲の終わりを探す目印にします。

  1. 掲載コードの先頭のDECLAREから、最後のORDER BYの末尾にあるセミコロンまでをコピーし、練習用のクエリタブに貼り付けます。コードに続く出力例は、実行後の照合に使います。
  2. 入力欄で文字の選択を解除して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と、掲載コードの入力値を順に見ると分かります。

  1. 5 ONで、予定420と430のLogNoがどちらもNULLであることを確認します。
  2. 2 LEFTで同じ予定番号を探します。予定420のLogNoは8103です。予定430のLogNoはここでもNULLです。
  3. 掲載コードの冒頭にある2つ目のINSERT INTOで、1番目の値が記録番号8103の組を探します。3番目の値はCheckStateで、HOLD(保留)と読めます。

予定420には保留の記録8103があり、予定430に対応する記録は0件です。5 ONではどちらも予定が残ります。6 WHEREではどちらも合格条件を通らず、一覧から外れます。

結果表が6つそろわないときは全文を実行し直す

実行中に止める必要がある場合は、練習用タブで[クエリの実行を取り消す]を選ぶか、Alt+Breakを押します。結果表が6つそろわない場合は、同じタブのメッセージ欄を読みます。表の宣言を求めるエラーが出たら、先頭のDECLAREを含めて実行したか確認します。

  1. 同じ練習用タブで、先頭のDECLAREから最後のORDER BYの末尾までが入力欄にあることを確認します。貼り直す場合は、[SQLを貼り付けて6つの結果表を出す]の手順1の範囲で入力欄を置き換えます。
  2. 文字の選択を解除してF5を押します。結果表が6つそろい、最初の1 INNERが3行、最後の6 WHEREが予定410と記録8101の1行であることを確認します。
  3. 練習が終わったらタブを閉じます。練習用の表は実行が終わると自動で片付きます。

掲載結果の取得環境は、ローカルのSQL Server 2022(16.00.1000)のtempdbです。接続に.NET SqlClientを使い、同じ接続で全文を2回実行した記録では、6つの表の順序、列名、全行が一致しています。SSMSの操作案内は公式資料に基づきます。

実際の台帳に応用するときは、予定を数えるのか、予定と記録の組を数えるのかを先に決めます。この練習では、入力した予定が3件でも、2 LEFTの結果は4行になります。