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)