【Oracle】ORA-01427の原因と解決方法｜single-row subquery returns more than one row・サブクエリの直し方

-- 元のSQLでエラーになる例
SELECT *
FROM orders o
WHERE o.customer_id = (
    SELECT c.customer_id
    FROM customers c
    WHERE c.rank = 'A'
);

-- まずサブクエリを単体で確認する
SELECT c.customer_id
FROM customers c
WHERE c.rank = 'A';

-- 何行返るか数える
SELECT COUNT(*)
FROM customers c
WHERE c.rank = 'A';

-- NG: サブクエリが複数行返るとORA-01427
SELECT *
FROM orders o
WHERE o.customer_id = (
    SELECT c.customer_id
    FROM customers c
    WHERE c.rank = 'A'
);

-- OK: 複数候補のどれかに一致すればよいならIN
SELECT *
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.rank = 'A'
);

-- OK: 顧客表にランクAの該当顧客が存在する注文を取得
SELECT *
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM customers c
    WHERE c.customer_id = o.customer_id
      AND c.rank = 'A'
);

-- NG: 複数の注文がある顧客ではORA-01427になる
SELECT c.customer_id,
       (SELECT o.order_id
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS order_id
FROM customers c;

-- OK: 注文を行として扱うならJOINにする
SELECT c.customer_id,
       o.order_id
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id;

-- NG: 同じ顧客に複数住所があるとORA-01427
SELECT o.order_id,
       (SELECT a.zip_code
        FROM customer_addresses a
        WHERE a.customer_id = o.customer_id) AS zip_code
FROM orders o;

-- OK: 使用する住所を条件で1行に絞る
SELECT o.order_id,
       (SELECT a.zip_code
        FROM customer_addresses a
        WHERE a.customer_id = o.customer_id
          AND a.address_type = 'MAIN'
          AND a.is_current = 'Y') AS zip_code
FROM orders o;

-- OK: 最新注文日を1値として返す
SELECT c.customer_id,
       (SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS latest_order_date
FROM customers c;

-- OK: 最小単価など、集約の意味が明確な場合に使う
SELECT p.product_id
FROM products p
WHERE p.price = (
    SELECT MIN(p2.price)
    FROM products p2
);

-- 顧客ごとの最新住所を1行選ぶ
SELECT o.order_id,
       (
           SELECT zip_code
           FROM (
               SELECT a.zip_code,
                      ROW_NUMBER() OVER (
                          PARTITION BY a.customer_id
                          ORDER BY a.updated_at DESC, a.address_id DESC
                      ) AS rn
               FROM customer_addresses a
               WHERE a.customer_id = o.customer_id
           ) x
           WHERE x.rn = 1
       ) AS zip_code
FROM orders o;

-- NG: サブクエリが複数行返るとORA-01427
SELECT *
FROM products p
WHERE p.price > (
    SELECT price
    FROM products
    WHERE category_id = 10
);

-- OK: いずれかの価格より高い
SELECT *
FROM products p
WHERE p.price > ANY (
    SELECT price
    FROM products
    WHERE category_id = 10
);

-- OK: すべての価格より高い
SELECT *
FROM products p
WHERE p.price > ALL (
    SELECT price
    FROM products
    WHERE category_id = 10
);

-- NG: 部署コードが重複していると複数行返る
UPDATE employees e
SET department_name = (
    SELECT d.department_name
    FROM departments d
    WHERE d.department_code = e.department_code
);

-- OK: 有効な1行に絞る
UPDATE employees e
SET department_name = (
    SELECT d.department_name
    FROM departments d
    WHERE d.department_code = e.department_code
      AND d.is_current = 'Y'
)
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_code = e.department_code
      AND d.is_current = 'Y'
);

-- 部署コードが一意である前提なのに複数行あるか確認する
SELECT department_code,
       COUNT(*) AS row_count
FROM departments
GROUP BY department_code
HAVING COUNT(*) > 1;

-- 有効行が複数あるかを確認する
SELECT department_code,
       COUNT(*) AS current_count
FROM departments
WHERE is_current = 'Y'
GROUP BY department_code
HAVING COUNT(*) > 1;

-- NG: THEN内のサブクエリが複数行返る可能性がある
SELECT c.customer_id,
       CASE
           WHEN c.rank = 'A' THEN (
               SELECT coupon_code
               FROM coupons
               WHERE rank = 'A'
           )
           ELSE NULL
       END AS coupon_code
FROM customers c;

-- OK: 1行に集約する、または別表としてJOINする
SELECT c.customer_id,
       CASE
           WHEN c.rank = 'A' THEN (
               SELECT MAX(coupon_code)
               FROM coupons
               WHERE rank = 'A'
           )
           ELSE NULL
       END AS coupon_code
FROM customers c;