Junções externas do estilo Oracle na cláusula WHERE

Para compatibilidade com a Oracle, o Amazon Redshift oferece suporte ao operador de junção externa da Oracle (+) nas condições de junção da cláusula WHERE. Esse operador deve ser usado apenas para definir condições de junção externas; não tente usar em outros contextos. Outros usos para este operador são ignorados silenciosamente na maioria dos casos.

Uma junção externa retorna todas as linhas que a junção interna equivalente deve retornar e linhas não correspondentes de uma ou de ambas as tabelas. Na cláusula FROM, você pode especificar junções esquerdas, direitas e externas. Na cláusula WHERE, você pode especificar somente junções externas esquerdas e direitas.

Para juntar as tabelas externas TABLE1 e TABLE2 e retornar linhas não correspondentes da TABLE1 (junção externa esquerda), especifique TABLE1 LEFT OUTER JOIN TABLE2 na cláusula ou aplique o operador (+) a todas as colunas de junção de TABLE2 na cláusula WHERE. Para todas as linhas na TABLE1 que não têm linhas correspondentes na TABLE2, o resultado da consulta contém nulos para quaisquer expressões de lista de seleção contendo colunas da TABLE2.

Para produzir o mesmo comportamento em todas as linhas na TABLE2 que não têm linhas correspondentes na TABLE1, especifique TABLE1 RIGHT OUTER JOIN TABLE2 na cláusula FROM ou aplique o operador (+) a todas as colunas de junção da TABLE1 na cláusula WHERE.

Sintaxe básica

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

A primeira condição equivale a:

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

A segunda condição equivale a:

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

nota

A sintaxe mostrada aqui abrange o caso simples de uma junção equivalente em um par de colunas de junção. Porém, outros tipos de condições de comparação e diversos pares de colunas de junção também são válidos.

Por exemplo, a cláusula WHERE a seguir define um junção externa em relação a dois pares de colunas. O operador (+) deve ser vinculado à mesma tabela em ambas as condições:

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

Observações de uso

Sempre que possível, use a sintaxe OUTER JOIN da cláusula FROM padrão em vez do operador (+) na cláusula WHERE. Consultas contendo o operador (+) estão sujeitas às seguintes regras:

• Você só pode usar o operador (+) na cláusula WHERE, e somente em referência a colunas de tabelas ou exibições.

• Você não pode aplicar o operador (+) a expressões. No entanto, uma expressão pode conter colunas que usam o operador (+). Por exemplo, a condição de junção a seguir retorna um erro de sintaxe:

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

  No entanto, a seguinte condição de junção é válida:

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

• Você não pode usar o operador (+) em um bloco de consulta que também contenha a sintaxe de junção da cláusula FROM.

• Se duas tabelas são adicionadas em diversas condições de junção, você deve usar o operador (+) em todas ou em nenhuma dessas condições. Uma junção com estilos mistos de sintaxe é executada como uma junção interna, sem aviso.

• O operador (+) não produzirá um uma junção externa se você juntar uma tabela na consulta externa com uma tabela que resulte de uma consulta interna.

• Para usar o operador (+) para juntar uma tabela externa na própria tabela, você deve definir aliases da tabela na cláusula FROM e fazer referência a eles na condição de junção:

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

• Você não pode combinar uma condição de junção que contenha o operador (+) com uma condição OR ou IN. Por exemplo:

  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.

• Em uma cláusula WHERE faz junções externas de mais de duas tabelas, o operador (+) pode ser aplicado somente uma vez a uma tabela específica. No exemplo a seguir, não é possível fazer referência à tabela SALES com o operador (+) em duas junções sucessivas.

  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.

• Se a condição de junção externa da cláusula WHERE se comparar a uma coluna da TABLE2 com uma constante, aplique o operador (+) à coluna. Se você não incluir o operador, as linhas de junções externas da TABLE1 que contêm nulos para a coluna restringida serão eliminadas. Consulte a seção de Exemplos abaixo.

Exemplos

A seguinte consulta de junção especifica uma junção esquerda externa das tabelas SALES e LISTING em suas colunas LISTID:

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

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

A seguinte consulta equivalente produz o mesmo resultado, mas usa a sintaxe de junção da cláusula FROM:

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

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

A tabela SALES não contém registros de todas as listagens na tabela LISTING, pois nem todas as listagens resultam em vendas. A consulta a seguir junta externamente as tabelas SALES e LISTING e retorna linhas da tabela LISTING mesmo quando a tabela SALES não retorna vendas para determinado ID de lista. As colunas PRICE e COMM, derivadas da tabela SALES, contêm nulos no conjunto de resultados para linhas não correspondentes.

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)

Observe que quando o operador de junção da cláusula WHERE é usado, a ordem das tabelas na cláusula FROM não importa.

Um exemplo de uma condição de junção externa mais complexa na cláusula WHERE é o caso em que a condição consiste em uma comparação entre duas tabelas comuns e uma comparação com uma constante:

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

Observe que o operador (+) é usado em dois lugares: primeiro na comparação de correspondência entre as tabelas e depois na condição de comparação para a coluna EVENTID. O resultado da sintaxe é a preservação das linhas de junção externa quando a restrição em EVENTID é avaliada. Se você remover o operador (+) da restrição EVENTID, a consulta trata a restrição como um filtro, não como parte da condição de junção externa. Por sua vez, as colunas de junção externa que contêm nulos para EVENTID são eliminadas do conjunto de resultados.

Veja aqui uma consulta completa que ilustra esse comportamento:

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)

A consulta equivalente usando a sintaxe de cláusula FROM é:

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

Se você remover o segundo operador (+) da versão da cláusula WHERE desta consulta, ela retornará somente 1 linha (a linha de 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)