WHERE 句の Oracle スタイルの外部結合 - Amazon Redshift
基本構文使用に関する注意事項例

WHERE 句の Oracle スタイルの外部結合

Oracle との互換性を目的として、Amazon Redshift は WHERE 句結合条件での Oracle の外部結合演算子 (+) をサポートします。この演算子は、外部結合条件の定義でのみ使用することを意図しています。他のコンテキストで使用しないでください。この演算子をその他の目的に使用すると、ほとんどの場合、メッセージを表示せずに無視します。

外部結合は、同等の内部結合が返す行と同じ行をすべて返します。それに加え、1 つまたは両方のテーブルから一致しない行も返します。FROM 句では、left、right、full の外部結合を指定できます。WHERE 句では、left または right の外部結合だけを指定できます。

TABLE1 と TABLE2 を外部結合し、TABLE1 (左側の外部結合) から一致しない行を返すには、FROM 句内で TABLE1 LEFT OUTER JOIN TABLE2 を指定するか、WHERE 句内で TABLE2 からのすべての結合列に (+) 演算子を適用します。TABLE2 と一致する行がない TABLE1 のすべての行の場合、TABLE2 からの列を含む SELECT リスト式に対するクエリ結果には Null が含まれます。

TABLE1 と一致する行がない TABLE2 のすべての行に対して同じ動作を生成するには、FROM 句で TABLE1 RIGHT OUTER JOIN TABLE2 を指定するか、WHERE 句内で TABLE1 からのすべての結合列に (+) 演算子を適用します。

基本構文

[ WHERE {
[ table1.column1 = table2.column1(+) ]
[ table1.column1(+) = table2.column1 ]
}

最初の条件は、以下と同等です。

from table1 left outer join table2
on table1.column1=table2.column1

2 番目の条件は、以下と同等です。

from table1 right outer join table2
on table1.column1=table2.column1
注記

ここに示す構文は、結合列の 1 ペアを介した等価結合のシンプルなケースを示しています。ただし、他のタイプの比較条件と結合列の複数のペアも有効です。

例えば、次の WHERE 句は、列の 2 つのペアを介して、外部結合を定義します。(+) 演算子は、両方の条件の同じテーブルにアタッチする必要があります。

where table1.col1 > table2.col1(+)
and table1.col2 = table2.col2(+)

使用に関する注意事項

可能な場合は、WHERE 句の (+) 演算子の代わりに、標準の FROM 句 Outer JOIN 構文を使用してください。(+) 演算子を含んでいるクエリは、次の規則に依存します。

  • (+) 演算子は WHERE 句内と、テーブルまたはビューから列への参照でのみ使用できます。

  • 式に (+) 演算子を適用することはできません。ただし、式に (+) 演算子を使用する列を含めることはできます。例えば、次の結合条件は、構文エラーを返します。

    event.eventid*10(+)=category.catid

    ただし、次の結合条件は有効です。

    event.eventid(+)*10=category.catid

  • FROM 句結合構文も含むクエリブロック内で (+) 演算子を使用することはできません。

  • 2 つのテーブルを複数の結合条件を介して結合する場合、(+) 演算子をこれらの条件のすべてで使用するか、すべてで使用しないことが必要です。構文スタイルが混在する結合は、内部結合として実行され、警告は出力されません。

  • 外部クエリ内のテーブルを内部クエリによって生成されたテーブルに結合する場合、(+) 演算子は外部結合を生成しません。

  • (+) 演算子を使って、テーブルを自分自身に外部結合するには、FROM 句内でテーブルエイリアスを定義し、結合条件でそのエイリアスを参照する必要があります。

    select count(*)
from event a, event b
where a.eventid(+)=b.catid;

count
-------
8798
(1 row)

  • (+) 演算子を含む結合条件を、OR 条件または IN 条件と組み合わせることはできません。次に例を示します。

    select count(*) from sales, listing
where sales.listid(+)=listing.listid or sales.salesid=0;
ERROR:  Outer join operator (+) not allowed in operand of OR or IN.

  • 3 つ以上のテーブルを外部結合する WHERE 句では、(+) 演算子は指定されたテーブルに一度して適用できません。次の例では、2 つの連続する結合で (+) 演算子を使って SALES テーブルを参照することはできません。

    select count(*) from sales, listing, event
where sales.listid(+)=listing.listid and sales.dateid(+)=date.dateid;
ERROR:  A table may be outer joined to at most one other table.

  • WHERE 句の外部結合条件で TABLE2 からの列を定数と比較する場合、(+) 演算子を列に適用します。演算子を含めない場合、TABLE1 から外部結合された行 (制限された行に対して null を含んでいる行) は削除されます。以下の「例」のセクションを参照してください。

例

次の結合クエリは、LISTID 列を介した SALES テーブルと LISTING テーブルの左外部結合を指定します。

select count(*)
from sales, listing
where sales.listid = listing.listid(+);

count
--------
172456
(1 row)

次の同等クエリは同じ結果を提供しますが、FROM 句結合構文を使用します。

select count(*)
from sales left outer join listing on sales.listid = listing.listid;

count
--------
172456
(1 row)

すべての一覧が販売に含まれるわけではないため、SALES テーブルに LISTING テーブル内のすべてのリストのレコードが含まれているわけではありません。次のクエリでは、SALES と LISTING を外部結合し、SALES テーブルが指定されたリスト ID に対して販売がないことをレポートした場合でも、LISTING からの行を返します。SALES テーブルから生成された PRICE と COMM 列には、一致しない行の結果セットにヌルが格納されています。

select listing.listid, sum(pricepaid) as price,
sum(commission) as comm
from listing, sales
where sales.listid(+) = listing.listid and listing.listid between 1 and 5
group by 1 order by 1;

listid | price  |  comm
--------+--------+--------
1 | 728.00 | 109.20
2 |        |
3 |        |
4 |  76.00 |  11.40
5 | 525.00 |  78.75
(5 rows)

WHERE 句の結合演算子を使用する場合、FROM 句のテーブルの順番は問題になりません。

WHERE 句のより複雑な外部結合条件の例としては、2 つのテーブル列間の比較と定数との比較による条件のケースがあります。

where category.catid=event.catid(+) and eventid(+)=796;

(+) 演算子は 2 つの場所で使用されることに注意してください。1 つ目はテーブル間の等価比較、2 つ目は EVENTID 列への条件の比較においてです。この構文の結果は、EVENTID に関する制限が評価される際、外部結合された行に保存されます。EVENTID 制限から (+) 演算子を削除すると、クエリはこの制限を、外部結合条件ではなく、フィルタとして処理します。この場合、EVENTID にヌルが格納されている外部結合された行は、結果セットから削除されます。

ここに、この動作を表現する完全なクエリを示します。

select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid(+)=796;

catname | catgroup | eventid
-----------+----------+---------
Classical | Concerts |
Jazz | Concerts |
MLB | Sports   |
MLS | Sports   |
Musicals | Shows    | 796
NBA | Sports   |
NFL | Sports   |
NHL | Sports   |
Opera | Shows    |
Plays | Shows    |
Pop | Concerts |
(11 rows)

FROM 句構文を使用した同等クエリは次のとおりです。

select catname, catgroup, eventid
from category left join event
on category.catid=event.catid and eventid=796;

このクエリの WHERE 句バージョンから 2 番目の (+) 演算子を削除した場合、1 行だけが返されます (eventid=796 となる行)。

select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid=796;

catname | catgroup | eventid
-----------+----------+---------
Musicals | Shows    | 796
(1 row)