ON句に書くのは結合条件であって絞り込み条件ではないことに注意しよう

令和３年秋データベーススペシャリスト試験　午前Ⅱ問８にJOINのに関する問題が出題されていました。

問題

出題されているテーブルと登録されているデータは以下のとおりです。

CREATE TABLE 社員取得資格 (
    社員コード VARCHAR(4) NOT NULL,
    資格 VARCHAR(2)
);

INSERT INTO 社員取得資格 VALUES ('S001', 'FE');
INSERT INTO 社員取得資格 VALUES ('S001', 'AP');
INSERT INTO 社員取得資格 VALUES ('S001', 'DB');
INSERT INTO 社員取得資格 VALUES ('S002', 'FE');
INSERT INTO 社員取得資格 VALUES ('S002', 'SM');
INSERT INTO 社員取得資格 VALUES ('S003', 'FE');
INSERT INTO 社員取得資格 VALUES ('S004', 'AP');
INSERT INTO 社員取得資格 VALUES ('S005', NULL);

INSERTした結果は以下のようになります。問題に記載されている範囲ではPKを持たないテーブルになります。

出題されている問題は、以下の結果が得られるようにするために、

[ a ]に入る字句を答える問題になります。

SELECT
    C1.社員コード, C1.資格 AS 資格１, C2.資格 AS 資格２
FROM
    社員取得資格 C1
    LEFT OUTER JOIN 社員取得資格 C2
[ a ]

この問題の正解は、

SELECT
    C1.社員コード, C1.資格 AS 資格１, C2.資格 AS 資格２
FROM
    社員取得資格 C1
    LEFT OUTER JOIN 社員取得資格 C2
    ON C1.社員コード = C2.社員コード
      AND C1.資格 = 'FE' AND C2.資格 = 'AP'
WHERE
    C1.資格 = 'FE'

になりますが、不正解の選択肢と共通している字句は

    ON C1.社員コード = C2.社員コード
      AND C1.資格 = 'FE' AND C2.資格 = 'AP'

で、自己結合のために使用するC1.社員コード = C2.社員コード以外にC1.資格 = 'FE' AND C2.資格 = 'AP'という条件があるようです。

ON句の条件が結果にどのように影響するのか

ON句に結合条件以外を指定するとどのように振る舞うのかを確認してみたいと思います。

自己結合すると何が得られるのか

まずは、社員コードで自己結合すると何が得られるかを確認してみましょう。

SELECT
    C1.社員コード, C1.資格 AS 資格１, C2.資格 AS 資格２
FROM
    社員取得資格 C1
    LEFT OUTER JOIN 社員取得資格 C2
    ON C1.社員コード = C2.社員コード

このSQLを実行すると、以下のような結果が得られます。

当然ですが、同じ社員コードのレコード同士のすべての掛け合わせが表示されます。この中には自分自身のレコードも含まれています。

ON句にC1.資格 = ‘FE’を指定するとどうなるのか

ON句には結合条件を指定するのですが、ここにC1.資格 = 'FE'を指定するとどうなるのでしょうか？

SELECT
    C1.社員コード, C1.資格 AS 資格１, C2.資格 AS 資格２
FROM
    社員取得資格 C1
    LEFT OUTER JOIN 社員取得資格 C2
    ON C1.社員コード = C2.社員コード
      AND C1.資格 = 'FE'

C1のうち資格 = 'FE'のみ結合できるようにする条件となるため、FE以外の資格の行は社員コードが一致しても結合されなくなります。

資格１がFEの行については資格２にすべての組み合わせが設定されていますが、資格１がFE以外の行については資格２がNULLになっていることがわかります。

ONにC2.資格 = ‘AP’を指定するとどうなるのか

一旦、C1.資格 = 'FE'の条件を外して、C2.資格 = 'AP'の条件を指定するとどうなるでしょうか？

SELECT
    C1.社員コード, C1.資格 AS 資格１, C2.資格 AS 資格２
FROM
    社員取得資格 C1
    LEFT OUTER JOIN 社員取得資格 C2
    ON C1.社員コード = C2.社員コード
      AND C2.資格 = 'AP'

C2のうち、資格='AP'のみ結合できるようにする条件となるため、AP以外の資格の行は社員コードが一致しても結合されなくなります。結合は左外部結合（LEFT OUTER JOIN）で行われているため、先ほどと異なり、結合相手のC2の行がない資格１の行は１行だけ残り、資格２はNULLになります。

APを資格として保有している社員のみ資格１と資格２（AP固定）が表示され、APを資格として保有していない社員は資格１のみ表示されます。

ON句にC1.資格 = ‘FE’ AND C2.資格 = ‘AP’の両方を指定するとどうなるのか

これまでの内容を踏まえると、あくまでも結合対象となる条件を指定しているだけですので、社員コードが一致するC1.資格 = 'FE'の行とC2.資格 = 'AP'が結合されます。このとき、左外部結合（LEFT OUTER JOIN）で結合しているため、C1の行は最低１行は残ります。

SELECT
    C1.社員コード, C1.資格 AS 資格１, C2.資格 AS 資格２
FROM
    社員取得資格 C1
    LEFT OUTER JOIN 社員取得資格 C2
    ON C1.社員コード = C2.社員コード
      AND C1.資格 = 'FE'
      AND C2.資格 = 'AP'

C1の資格がFEでない行はC2が結合されず、C2がAPでなければC1がFEでも結合されないため、資格１がFEでない行は資格２はNULLになり、資格２がAPになっている行は資格１がFEになります。

WHERE句でC1.資格 = ‘FE’の行だけフィルタする必要がある　

ここまで見てみると資格１にはFE以外の行があることがわかります。これは左外部結合（LEFT OUTER JOIN）で結合したことでC1の行が消えないためです。得られるべき結果の資格１はFEのみになっているため、FE以外を除外するようにフィルタする必要があります。

これを実現するにはC1.資格 = 'FE'の条件をWHERE句に追加することで実現します。

SELECT
    C1.社員コード, C1.資格 AS 資格１, C2.資格 AS 資格２
FROM
    社員取得資格 C1
    LEFT OUTER JOIN 社員取得資格 C2
    ON C1.社員コード = C2.社員コード
      AND C1.資格 = 'FE'
      AND C2.資格 = 'AP'
WHERE
    C1.資格 = 'FE'

代替案の検討

INNER JOINに変更すればWHERE句は不要か

左外部結合（LEFT OUTER JOIN）によって余計な行が残るという話をしてきましたが、内部結合（INNER JOIN）に変更したらどうなるでしょうか？

WHERE句を含まないINNER JOINで自己結合したSQLを作成して実行してみます。

SELECT
    C1.社員コード, C1.資格 AS 資格１, C2.資格 AS 資格２
FROM
    社員取得資格 C1
    INNER JOIN 社員取得資格 C2
    ON C1.社員コード = C2.社員コード
      AND C1.資格 = 'FE'
      AND C2.資格 = 'AP'

この条件ではFEとAPの両方の資格を保有している社員の行のみが表示されるようになります。

結合しないで同じ結果を得ることは可能

左外部結合を使った場合は、FEの資格を保有している社員を表示し、APの資格も保有していたら表示する、という条件をあらわしています。

実務でこのようなSQLがあるかといわれるとそれほど多くないと思いますが、私が書くなら結合せずに書くと思います。なぜなら、ON句内に結合条件以外の条件を書くことは、可読性が高くなく、誤った解釈をするリスクがあるためで、ON句には2つのテーブル結合するためのキー情報のみ書きたいと考えているからです。絞り込み条件に見える条件は極力WHERE句に書くようにすることで、SQLの初心者であっても読み間違えないようにすることが重要だと考えています。

では、どうするかというと結合せずにCASE句を使って資格２の条件を記載します。

SELECT
    C1.社員コード,
    C1.資格 AS 資格１,
    CASE
        WHEN EXISTS (SELECT 1 FROM 社員取得資格 C2 WHERE C1.社員コード = C2.社員コード AND C2.資格 = 'AP') THEN 'AP'
        ELSE NULL
    END AS 資格2
FROM
    社員取得資格 C1
WHERE
    C1.資格 = 'FE'

このSQLでは必須となるFEを保有している社員を単純に抽出し、資格２には同社員がAPを保有していれば'AP'と出力し、保有していなければNULLを出力するようにしています。

あくまでもFEを保有している社員を抽出することが主眼とし、＋αの情報としてAPを保有しているかを抽出するようにSQLを書いています。

まとめ

ON句にキーの結合条件以外の条件が出てくるとドキッとしますが、ひとつひとつ丁寧に確認していくことでそれが結合する条件であることがわかってきます。

試験対策としてこのような動作は実機で確認して理解しておくことは重要ですが、実務においては誤解されないかを十分に確認した上で使った方がよいと思います。ON句で書くこととWHERE句で書くことは等価な場合もありますが、等価でない場合もありますので、意図した結果を得られるのかを十分に検証してください。