SQL 副問い合わせ使い方について

質問

初心者です。
フォームに入力された、EMPNOとEMP_IDに一致するデータ（複数の場合あり）を取得するSQL文なのですが、参考にしようとしているコードがいまいち理解できなくて困っています。特にグループ化と結合のあたりがよくわかりません。どなたか、詳しく教えていただけないでしょうか？よろしくお願いします
SELECT EMPINFO_A.EMPNO, EMPINFO_A.EMP_ID,
EMPCD_CDA.CDA_NAME || EMPCD_CDB.CDB_NAME AS EMPCD_CDNM,
NVL(EMPINFO_MAP_VIRTUAL.EMPINFO_COUNT, 0) AS EMPINFO_MAP_COUNT
FROM EMPINFO_A, EMPCD_CDA, EMPCD_CDB
(SELECT COUNT(*) AS EMPINFO_MAP_COUNT, EMPNO,EMP_ID
FROM EMPINF0_MAP
WHERE EMPNO = :P_EMPNO
AND EMP_ID = :P_EMP_ID
GROUP BY EMPNO, EMP_ID)
EMPINFO_MAP_VIRTUAL
WHERE EMPINFO_A._EMPNO = :P_EMPNO
AND EMPINFO_A.EMP_ID = :P_EMP_ID
AND EMPINFO_A.EMPNO = EMPINFO_MAP_VIRTUAL.EMPNO(+)
AND EMPINFO_A.EMP_ID = EEMPINFO_MAP_VIRTUAL.EMP_ID(+)

回答No.2

jamshid6

タイプミスと、EMPCD_CDA, EMPCD_CDBに結合条件が指定されていない点は私も気になりました。
#1さんの回答で十分説明されているので、補足みたいなものです。
そもそもGROUP BYする必要があるのかということについですが、

今使われている以下のサブクエリでは、GROUP BYを使っているため、結果は0行または1行です。
GROUP BYを使うと、キー単位にグルーピングする結果、キーのある場合しか結果が返らないからです。

SELECT COUNT(*) AS EMPINFO_MAP_COUNT, EMPNO,EMP_ID
FROM EMPINF0_MAP
WHERE EMPNO = :P_EMPNO
AND EMP_ID = :P_EMP_ID
GROUP BY EMPNO, EMP_ID

一方、単純に件数だけを取るようにすると、対象がなくても「0件」と返ってきますから、結果は必ず1行返ります。

SELECT COUNT(*) AS EMPINFO_MAP_COUNT
FROM EMPINF0_MAP
WHERE EMPNO = :P_EMPNO
AND EMP_ID = :P_EMP_ID

結果が必ず１行返るので、外部結合もNVLをする必要もありません。
その場合はクエリは以下の通りです。

SELECT
EMPINFO_A.EMPNO,
EMPINFO_A.EMP_ID,
EMPCD_CDA.CDA_NAME || EMPCD_CDB.CDB_NAME AS EMPCD_CDNM,
EMPINFO_MAP_VIRTUAL.EMPINFO_COUNT AS EMPINFO_MAP_COUNT
FROM EMPINFO_A, EMPCD_CDA, EMPCD_CDB,
(SELECT COUNT(*) AS EMPINFO_MAP_COUNT
FROM EMPINF0_MAP
WHERE EMPNO = :P_EMPNO
AND EMP_ID = :P_EMP_ID) EMPINFO_MAP_VIRTUAL
WHERE EMPINFO_A._EMPNO = :P_EMPNO
AND EMPINFO_A.EMP_ID = :P_EMP_ID

お礼

jamshid6さま、補足説明ありがとうございます。参考にさせていただきたいと思います。これからもっと勉強します！

回答No.1

utakataXEX

>特にグループ化と結合のあたりがよくわかりません

「このSQLで、どうして意図した結果が得られるのかがわからない」のか、「グループ化と結合のSQLの基礎的な構文自体がよくわからない」のかがわかりませんが、とりあえず想像で書いて行きます。

まず、マスタテーブルと思われるEMPCD_CDA, EMPCD_CDBを特定する条件が無いので（書き漏れ？）、話をわかりやすくするため、外します。（ついでに明らかな書き間違いも）

・EMPINFO_Aは基本的な社員マスタ？
・EMPINF0_MAPはEMPNO、EMP_IDで社員マスタと紐付くトランザクションかサブマスタ
・フォームの入力値からEMPINFO_Aを表示したい
・EMPINF0_MAPをEMPNO、EMP_ID単位で何件あるかを表示したい
・EMPINF0_MAPは、必ずしも社員全員分の情報があるわけではない
（EMPINFO_Aに紐づくEMPINF0_MAPが無い場合はカウント0で表示）

これが想像される要件です。まず、フォームの入力値からEMPINFO_Aを表示する部分だけ書いてみます。これはわかりますよね。

SELECT EMPINFO_A.EMPNO, EMPINFO_A.EMP_ID
FROM EMPINFO_A
WHERE EMPINFO_A._EMPNO = :P_EMPNO
AND EMPINFO_A.EMP_ID = :P_EMP_ID

次に()で括られたインラインビューEMPINFO_MAP_VIRTUALの部分のみ。
EMPINF0_MAPをEMPNO、EMP_ID単位で何件あるかを取得します。
この「何々単位で集計」と言う要件を満たすために、GROUP BYを使います。

SELECT COUNT(*) AS EMPINFO_MAP_COUNT, EMPNO,EMP_ID
FROM EMPINF0_MAP
WHERE EMPNO = :P_EMPNO
AND EMP_ID = :P_EMP_ID
GROUP BY EMPNO, EMP_ID) EMPINFO_MAP_VIRTUAL

この２つを結合したのが以下のSQLになります。

SELECT EMPINFO_A.EMPNO, EMPINFO_A.EMP_ID,
NVL(EMPINFO_MAP_VIRTUAL.EMPINFO_COUNT, 0) AS EMPINFO_MAP_COUNT
FROM EMPINFO_A,
(SELECT COUNT(*) AS EMPINFO_MAP_COUNT, EMPNO,EMP_ID
FROM EMPINF0_MAP
WHERE EMPNO = :P_EMPNO
AND EMP_ID = :P_EMP_ID
GROUP BY EMPNO, EMP_ID) EMPINFO_MAP_VIRTUAL
WHERE EMPINFO_A._EMPNO = :P_EMPNO
AND EMPINFO_A.EMP_ID = :P_EMP_ID
AND EMPINFO_A.EMPNO = EMPINFO_MAP_VIRTUAL.EMPNO(+)
AND EMPINFO_A.EMP_ID = EMPINFO_MAP_VIRTUAL.EMP_ID(+)

入力値の社員キーでヒットするEMPINF0_MAPは、必ずしも社員全員分の情報があるわけではないですが、入力値の社員キーでヒットするEMPINFO_Aはすべて表示しなければいけません。その場合は、「外部結合」を使うので、EMPINFO_MAP_VIRTUALの結合条件のところに「(+)」を指定します。

外部結合は、ANSI標準の LEFT JOIN で書いた方が、個人的にはわかりやすい気がしますが、バージョンが書かれていないので何とも言えません。
（FORMS側の制限もあるかもしれないし）

外部結合を使うと、「紐付かないデータはNULL」になります。
この場合で言えば、EMPINFO_MAP_VIRTUAL.EMPINFO_COUNTがそれにあたります。
NVLを使って、件数NULLはZEROに補完します。

後は、名称を取得するためのマスタテーブルと思われるEMPCD_CDA, EMPCD_CDBとの結合条件を補完して完成。
（質問のSQLから欠落しているので、何で紐付ければよいかはわかりません）

お礼

utakataXEXさま、詳しい説明ありがとうございます！結合条件とか抜けててすみません。書き忘れました。インラインビューのあたりが一番疑問でした。使い方等知らなかったこともあり、ネットで検索する際も的を得ていませんでした。勉強させていただきました。貴重なお時間をありがとうございます！