Oracle 12c以降で推奨!(+) 演算子に代わる「INNER JOIN ... WITH MATCHING」句の使い方
Oracle (+) 演算子:詳細解説
Oracle (+) 演算子は、外部結合と呼ばれる特殊な結合操作において使用されます。外部結合では、結合条件を満たす行だけでなく、条件を満たさない行も結果として含めることができます。
基本的な構文
SELECT *
FROM table1
[OUTER] JOIN table2
ON table1.column1 (+) = table2.column2;
演算子の種類
- (+) 演算子: 左結合を実行します。デフォルトの演算子です。
- (+) RIGHT 演算子: 右結合を実行します。
- (+) FULL OUTER JOIN: 完全外部結合を実行します。
各結合の種類
- 左結合: 結合条件を満たす行と、左表のすべての行を出力します。右表の該当する行がない場合は、NULL値で補われます。
例
顧客テーブル (customers) と 注文テーブル (orders) を結合する例を考えてみましょう。
customers テーブル:
customer_id | name |
---|---|
1 | 田中一郎 |
2 | 佐藤二郎 |
3 | 鈴木三郎 |
orders テーブル:
order_id | customer_id | product |
---|---|---|
1 | 1 | テレビ |
2 | 2 | パソコン |
3 | 1 | スマホ |
左結合
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
結果:
customer_id | name | order_id | product |
---|---|---|---|
1 | 田中一郎 | 1 | テレビ |
1 | 田中一郎 | 3 | スマホ |
2 | 佐藤二郎 | 2 | パソコン |
3 | 鈴木三郎 | NULL | NULL |
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;
customer_id | name | order_id | product |
---|---|---|---|
1 | 田中一郎 | 1 | テレビ |
1 | 田中一郎 | 3 | スマホ |
2 | 佐藤二郎 | 2 | パソコン |
NULL | NULL | 4 | カメラ |
NULL | NULL | 5 | ゲーム機 |
完全外部結合
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
customer_id | name | order_id | product |
---|---|---|---|
1 | 田中一郎 | 1 | テレビ |
1 | 田中一郎 | 3 | スマホ |
2 | 佐藤二郎 | 2 | パソコン |
3 | 鈴木三郎 | NULL | NULL |
NULL | NULL | 4 | カメラ |
NULL | NULL | 5 | ゲーム機 |
補足
- (+) 演算子は、パフォーマンス上の影響が大きくなる可能性があります。結合するテーブルが大きい場合は、注意が必要です。
- Oracle 12c 以降では、(+) 演算子の代わりに "INNER JOIN ... WITH MATCHING" 句を使用することが推奨されています。構文がより直感的で、可読性も向上します。
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
WITH MATCHING (
c.customer_id
);
customer_id | name | order_id | product |
---|---|---|---|
1 | 田中一郎 | 1 | テレビ |
1 | 田中一郎 | 3 | スマホ |
2 | 佐藤二郎 | 2 | パソコン |
解説
INNER JOIN
句は、結合条件を満たす行のみを出力します。WITH MATCHING
句は、左表のすべての行を結果に含めるように指定します。結合条件を満たさない行は、NULL値で補われます。
このコードは、左結合 と同じ結果を出力します。
WITH MATCHING
句は、Oracle 12c 以降でのみ使用できます。- 複数の列を結合条件として指定する場合は、カンマ区切りで列名を列挙します。
WITH MATCHING (
c.customer_id,
c.name
);
完全外部結合 を実現するには、以下のコードのように LEFT OUTER JOIN
句と RIGHT OUTER JOIN
句を組み合わせることができます。
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
LEFT OUTER JOIN orders o
ON c.customer_id = o.customer_id
WITH MATCHING (
c.customer_id
)
UNION ALL
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
RIGHT OUTER JOIN orders o
ON c.customer_id = o.customer_id
WITH MATCHING (
o.order_id,
o.product
);
Oracle (+) 演算子は、外部結合を実行するために使用される特殊な演算子ですが、非推奨となっています。代替手段として、以下の方法が推奨されています。
INNER JOIN ... WITH MATCHING
説明:
INNER JOIN ... WITH MATCHING
句は、Oracle 12c 以降で導入された構文です。(+) 演算子と同等の機能を提供しますが、構文がより直感的で、可読性も向上します。
例:
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
WITH MATCHING (
c.customer_id
);
利点:
- (+) 演算子よりも分かりやすい構文
- 可読性が高い
- Oracle 12c 以降の最新バージョンで推奨されている
- Oracle 12c より前のバージョンでは使用できない
LEFT OUTER JOIN / RIGHT OUTER JOIN
LEFT OUTER JOIN
と RIGHT OUTER JOIN
を組み合わせることで、外部結合を実現できます。それぞれの結合で WITH MATCHING
句を使用して、必要な列を結果に含めます。
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
LEFT OUTER JOIN orders o
ON c.customer_id = o.customer_id
WITH MATCHING (
c.customer_id
)
UNION ALL
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
RIGHT OUTER JOIN orders o
ON c.customer_id = o.customer_id
WITH MATCHING (
o.order_id,
o.product
);
- 柔軟性の高い結合が可能
- コードが冗長になる
CASE 式
CASE
式を使用して、結合条件を満たす行と、満たさない行をそれぞれ処理することができます。
SELECT c.customer_id, c.name,
CASE
WHEN o.order_id IS NULL THEN '注文なし'
ELSE o.order_id
END AS order_id,
CASE
WHEN o.order_id IS NULL THEN NULL
ELSE o.product
END AS product
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
- 複雑な結合条件にも対応できる
- パフォーマンスが低下する可能性がある
状況に応じた適切な方法を選択
上記で紹介した方法はそれぞれ、利点と欠点があります。状況に応じて適切な方法を選択することが重要です。
- Oracle (+) 演算子は非推奨となっているため、新規の開発では使用しないことを推奨します。
- 上記以外にも、状況によってはサブクエリやCTE (Common Table Expression) を利用する方法なども考えられます。
sql oracle join