従業員と部門の関係を表すテーブルにおいて、各従業員に対して主となる部門IDを特定する問題を考えます。複数の部門に所属する従業員については、primary_flagが'Y'のレコードが主部門を示します。一方、単一の部門にのみ所属する従業員については、その部門が主部門となります。

この問題を解決する代表的な方法として、UNION演算子を用いた方法があります。このアプローチでは、二つの異なる条件で抽出した結果を結合します。

SELECT
    emp_id,
    dept_id
FROM
    Employee
GROUP BY
    emp_id
HAVING
    COUNT(dept_id) = 1

UNION

SELECT
    emp_id,
    dept_id
FROM
    Employee
WHERE
    primary_flag = 'Y';

最初のSELECT句では、GROUP BYとHAVING句を用いて、部門数が1つだけの従業員を特定しています。二つ目のSELECT句では、主部門フラグが'Y'に設定されている全てのレコードを取得します。UNIONは重複する結果行を自動的に除去するため、最終的に各従業員に対して主部門が一つずつ返されます。

別の手法として、ウィンドウ関数を活用する方法もあります。この方法では、副問い合わせ内で各従業員の総所属部門数を計算し、それを基に外部のWHERE句でフィルタリングを行います。

SELECT emp_id, dept_id
FROM (
    SELECT
        emp_id,
        dept_id,
        primary_flag,
        COUNT(*) OVER (PARTITION BY emp_id) AS dept_count
    FROM Employee
) AS subquery
WHERE dept_count = 1 OR primary_flag = 'Y';

副問い合わせ内のCOUNT(*) OVER (PARTITION BY emp_id) AS dept_countはウィンドウ関数の一例です。PARTITION BY emp_idにより、同じemp_idを持つ行ごとにグループ（パーティション）が形成され、そのグループ内の行数がdept_count列として各行に追加されます。これにより、元のレコード情報を失わずに各従業員の総所属数を取得できます。

例えば、以下のようなテーブルを考えます。

ウィンドウ関数を用いた副問い合わせの結果は以下のようになります。dept_count列が追加されている点に注目してください。

その後、外部クエリのWHERE dept_count = 1 OR primary_flag = 'Y'条件によって、単一所属者（dept_count = 1）と主部門フラグが'Y'の行が抽出されます。ウィンドウ関数を使用することで、集約を行っても元の行が保持されるため、後段のフィルタリングが可能になります。