WHERE 子句中 Oracle 樣式的外部聯結 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

WHERE 子句中 Oracle 樣式的外部聯結

為了提供 Oracle 相容性,Amazon Redshift 在 WHERE 子句聯結條件中支援 Oracle 外部聯結運算子 (+)。此運算子主要僅用於定義外部聯結條件;請勿嘗試在其他內容中使用它。大多數情況下,此運算子的其他用途都會加以忽略,且不會顯示任何訊息。

外部聯結會傳回對等內部聯結傳回的所有資料列,加上其中一個資料表或兩個資料表的不相符資料列。在 FROM 子句中,您可以指定左、右和完整外部聯結。在 WHERE 子句中,您只能指定左和右外部聯結。

若要對 TABLE1 和 TABLE2 進行外部聯結並從 TABLE1 傳回不相符的資料列 (左外部聯結),請在 FROM 子句中指定 TABLE1 LEFT OUTER JOIN TABLE2,或在 WHERE 子句中對來自 TABLE2 的所有聯結資料欄套用 (+) 運算子。對於 TABLE1 中在 TABLE2 中沒有相符資料列的所有資料列,查詢的結果會對包含 TABLE2 中資料欄的任何選取清單表達式包含 null。

若要對 TABLE2 中在 TABLE1 中沒有相符資料列的所有資料列產生相同的行為,請在 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

第二個條件相當於:

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

這裡顯示的語法採用一個聯結資料欄配對說明簡單的 equijoin 案例。不過,其他類型的比較條件和多個聯結資料欄配對同樣有效。

例如,下列 WHERE 子句會定義兩個資料欄配對之間的外部聯結。(+) 運算子在這兩個條件中必須連接至相同資料表:

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

使用須知

盡可能使用標準 FROM 子句 OUTER JOIN 語法,而不要在 WHERE 子句中使用 (+) 運算子。包含 (+) 運算子的查詢受到下列規則限制:

  • 您只能在 WHERE 子句中使用 (+) 運算子,而且只能參考資料表或檢視的資料欄。

  • 您無法將 (+) 運算子套用至表達式。不過,表達式可包含使用 (+) 運算子的資料欄。例如,下列聯結條件會傳回語法錯誤:

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

    不過,下列聯結條件有效:

    event.eventid(+)*10=category.catid
  • 您無法在同時包含 FROM 子句聯結語法的查詢區塊中使用 (+) 運算子。

  • 若兩個資料表是透過多個聯結條件聯結,您必須在所有條件中使用 (+) 運算子,或完全不使用。具有混合語法樣式的聯結會作為內部聯結運行,但不會產生警告。

  • 若您將外部查詢中的資料表與產生自內部查詢的資料表聯結,則 (+) 運算子不會產生外部聯結。

  • 若要使用 (+) 運算子將資料表與其本身進行外部聯結,您必須在 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.
  • 在與兩個以上資料表進行外部聯結的 WHERE 子句中,(+) 運算子只能對特定資料表套用一次。在以下範例中,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 進行外部聯結,並從 LISTING 傳回資料列,即使 SALES 資料表回報特定清單 ID 沒有銷售。PRICE 和 COMM 資料欄衍生自 SALES 資料表,其結果集中的不相符資料列會包含 null。

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 子句中較複雜的外部聯結條件範例,就是條件由兩個資料表資料欄之間的比較,以及與常數的比較所構成:

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

請注意,(+) 運算子會在兩處使用:一處是資料表之間的對等比較中,另一處是 EVENTID 資料欄的比較條件中。此語法的結果是在評估 EVENTID 的限制時,保留外部聯結資料列。若您從 EVENTID 限制中移除 (+) 運算子,則查詢會將此限制視為篩選條件,而非外部聯結條件的一部分。接著就會從結果集中消除 EVENTID 包含 null 的外部聯結資料列。

以下是說明此行為的完整查詢:

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 子句版本中移除第二個 (+) 運算子,則只會傳回 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)