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)