WHERE 子句中的 Oracle 样式的外部联接 - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

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(+)

使用说明

如果可能,请在 WHERE 子句中使用标准 FROM 子句 OUTER JOIN 语法而非 (+) 运算符。包含 (+) 运算符的查询需遵循以下规则:

  • 只能在 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 值)。请参阅下面的“示例”部分。

示例

以下联接查询指定 SALES 和 LISTING 表的左外部联接(基于其 LISTID 列):

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)