Oracle WHERE句の奥深さを探る：(+)演算子の詳細解説

OracleのWHERE句における(+)演算子の役割

行の結合

(+)演算子は、外部結合と呼ばれる特殊な結合方法を指定するために使用されます。外部結合は、結合条件を満たす行だけでなく、満たさない行も結果に含めることができます。

1 左外部結合

左外部結合は、左側のテーブルのすべての行を結果に含め、右側のテーブルと一致する行があれば結合します。一致する行がない場合は、右側のテーブルの列はNULL値で表示されます。

SELECT *
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id
(+)
WHERE employees.salary > 100000;

上記の例では、給与が10万円以上の従業員全員と、その所属部署情報（存在する場合）を出力します。所属部署が存在しない従業員は、部署情報がNULL値で表示されます。

2 右外部結合

SELECT *
FROM departments
RIGHT JOIN employees
ON departments.department_id = employees.department_id
(+)
WHERE departments.name = 'Sales';

3 全外部結合

SELECT *
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id
(+)
WHERE employees.salary > 100000 OR departments.name = 'Sales';

上記の例では、給与が10万円以上の従業員と、「Sales」部門に所属する従業員全員を出力します。両方の条件に合致する行は、重複して表示されます。

NULL値の処理

(+)演算子は、NULL値の処理にも影響を与えます。

1 IS NULL と IS NOT NULL

(+)演算子を併用すると、IS NULLやIS NOT NULL演算子の動作が変化します。

SELECT *
FROM employees
WHERE department_id IS NULL
(+)

上記の例では、部署IDがNULLの従業員のみを出力します。(+)演算子がない場合、NULL値は検索条件に一致しないため、結果は空になります。

2 COALESCE と NVL

(+)演算子は、COALESCEやNVL関数と組み合わせて、NULL値を別の値に置き換えることもできます。

SELECT COALESCE(department_id, '未所属')
FROM employees
(+)

上記の例では、部署IDがNULLの場合、「未所属」という文字列を出力します。

OracleのWHERE句における(+)演算子は、外部結合とNULL値の処理に関連する特殊な演算子です。外部結合を使用することで、結合条件を満たさない行も結果に含めることができ、NULL値の処理も柔軟に行えます。

CREATE TABLE employees (
  employee_id NUMBER(10),
  name VARCHAR2(50),
  department_id NUMBER(10),
  salary NUMBER(10)
);

INSERT INTO employees (employee_id, name, department_id, salary)
VALUES (1, 'John Doe', 10, 100000);
INSERT INTO employees (employee_id, name, department_id, salary)
VALUES (2, 'Jane Doe', 20, 120000);
INSERT INTO employees (employee_id, name, department_id, salary)
VALUES (3, 'Mike Jones', NULL, 150000);

CREATE TABLE departments (
  department_id NUMBER(10),
  name VARCHAR2(50)
);

INSERT INTO departments (department_id, name)
VALUES (10, 'Sales');
INSERT INTO departments (department_id, name)
VALUES (20, 'Marketing');

サンプルクエリ

-- 左外部結合

SELECT *
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id
(+)
WHERE employees.salary > 100000;

-- 右外部結合

SELECT *
FROM departments
RIGHT JOIN employees
ON departments.department_id = employees.department_id
(+)
WHERE departments.name = 'Sales';

-- 全外部結合

SELECT *
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id
(+)
WHERE employees.salary > 100000 OR departments.name = 'Sales';

-- IS NULL と (+)

SELECT *
FROM employees
WHERE department_id IS NULL
(+)

-- COALESCE と (+)

SELECT COALESCE(department_id, '未所属')
FROM employees
(+)

これらのクエリを実行することで、(+)演算子の使い方を理解することができます。

OracleのWHERE句における(+)演算子の代替方法

外部結合

(+)演算子で実現できる外部結合は、INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOINなどの標準的な結合方法と組み合わせて実現できます。

例：左外部結合

SELECT *
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id
OR employees.department_id IS NULL;

上記の例では、INNER JOINとIS NULL演算子を使用して、左外部結合を実現しています。

NULL値の処理

(+)演算子で実現できるNULL値の処理は、CASE式、COALESCE関数、NVL関数などの関数を使用して代替できます。

例：NULL値を「未所属」に置き換える

SELECT CASE WHEN department_id IS NULL THEN '未所属' ELSE department_id END
FROM employees;

(+)演算子は便利な演算子ですが、他の方法で代替することも可能です。状況に応じて、適切な方法を選択してください。