Outer join di tipo Oracle nella clausola WHERE

Per la compatibilità con Oracle, Amazon Redshift supporta l'operatore outer join Oracle (+) nelle condizioni di join della clausola WHERE. Questo operatore è destinato all'uso solo nella definizione delle condizioni di outer join; non provare a usarlo in altri contesti. Altri usi di questo operatore sono silenziosamente ignorati nella maggior parte dei casi.

Un outer join restituisce tutte le righe che l'inner join equivalente restituirebbe, più le righe non corrispondenti da una o entrambe le tabelle. Nella clausola FROM, puoi specificare gli outer join sinistro, destro e completo. Nella clausola WHERE, puoi specificare solo gli outer join destro.

Per l'outer join delle tabelle TABLE1 e TABLE2 e per restituire le righe non corrispondenti da TABLE1 (un outer join sinistro), specifica TABLE1 LEFT OUTER JOIN TABLE2 nella clausola FROM o applica l'operatore (+) a tutti i join colonne da TABLE2 nella clausola WHERE. Per tutte le righe in TABLE1 che non hanno righe corrispondenti in TABLE2, il risultato della query contiene valori null per qualsiasi espressione dell'elenco di selezione contenente colonne da TABLE2.

Per produrre lo stesso comportamento per tutte le righe in TABLE2 che non hanno righe corrispondenti in TABLE1, specifica TABLE1 RIGHT OUTER JOIN TABLE2 nella clausola FROM o applica l'operatore (+) a tutti i join colonne da TABLE1 nella clausola WHERE.

Sintassi di base

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

La prima condizione è equivalente a:

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

La seconda condizione è equivalente a:

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

Nota

La sintassi qui mostrata copre il caso semplice di un equijoin su una coppia di colonne di unione. Tuttavia, sono validi anche altri tipi di condizioni di confronto e più coppie di colonne di unione.

Ad esempio, la seguente clausola WHERE definisce un outer join su due coppie di colonne. L'operatore (+) deve essere collegato alla stessa tabella in entrambe le condizioni:

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

Note per l'utilizzo

Se possibile, utilizza la sintassi OUTER JOIN della clausola FROM standard anziché l'operatore (+) nella clausola WHERE. Le query che contengono l'operatore (+) sono soggette alle seguenti regole:

• Puoi utilizzare solo l'operatore (+) nella clausola WHERE e solo in riferimento alle colonne di tabelle o viste.

• Non puoi applicare l'operatore (+) alle espressioni. Tuttavia, un'espressione può contenere colonne che utilizzano l'operatore (+). Ad esempio, la seguente condizione di join restituisce un errore di sintassi:

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

  Tuttavia, la seguente condizione di join è valida:

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

• Non puoi utilizzare l'operatore (+) in un blocco di query che contiene anche la sintassi di join della clausola FROM.

• Se due tabelle sono unite in più condizioni di join, è necessario utilizzare l'operatore (+) in tutte o in nessuna di queste condizioni. Un join con stili di sintassi misti viene eseguito come inner join, senza avvertenza.

• L'operatore (+) non produce un outer join se si aggiunge una tabella nella query esterna con una tabella risultante da una query interna.

• Per utilizzare l'operatore (+) nell'outer join di una tabella su se stessa, è necessario definire gli alias di tabella nella clausola FROM e farvi riferimento nella condizione di join:

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

• Non puoi combinare una condizione di join che contiene l'operatore (+) con una condizione OR o una condizione IN. Ad esempio:

  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.

• In una clausola WHERE con l'outer join di più di due tabelle, l'operatore (+) può essere applicato solo una volta a una determinata tabella. Nell'esempio seguente, alla tabella SALES non si può fare riferimento con l'operatore (+) in due join successivi.

  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 la condizione dell'outer join della clausola WHERE confronta una colonna della TABLE2 con una costante, applica l'operatore (+) alla colonna. Se non includi l'operatore, vengono eliminate le righe con l'outer join della TABLE1 che contengono valori null per la colonna con restrizioni. Vedi di seguito la sezione Esempi.

Esempi

La seguente query di join specifica un outer join sinistro delle tabelle SALES e LISTING sulle rispettive colonne LISTID:

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

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

La seguente query equivalente produce lo stesso risultato ma utilizza la sintassi di join della clausola FROM:

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

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

La tabella SALES non contiene i record di tutti gli elenchi della tabella LISTING perché non tutti gli elenchi restituiscono vendite. La seguente query con gli outer join di SALES e LISTING restituisce le righe da LISTING anche quando la tabella SALES non restituisce le vendite per un determinato ID elenco. Le colonne PRICE e COMM, derivate dalla tabella SALES, contengono valori null nel set di risultati per le righe non corrispondenti.

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)

Tieni presente che quando viene utilizzato l'operatore join della clausola WHERE, non ha importanza l'ordine delle tabelle nella clausola FROM.

Un esempio di una condizione outer join più complessa nella clausola WHERE è il caso in cui la condizione consiste in un confronto tra due colonne di tabella e un confronto con una costante:

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

L'operatore (+) viene utilizzato in due punti: prima nel confronto di uguaglianza tra le tabelle e poi nella condizione di confronto per la colonna EVENTID. Il risultato di questa sintassi è la conservazione delle righe di outer join quando viene valutata la limitazione su EVENTID. Se rimuovi l'operatore (+) dalla restrizione EVENTID, la query considera questa limitazione come un filtro e non come parte della condizione di outer join. A loro volta, le righe dell'outer join che contengono valori null per EVENTID vengono eliminate dal set di risultati.

Ecco una query completa che illustra questo 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)

La query equivalente con la sintassi della clausola FROM è la seguente:

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

Se rimuovi il secondo operatore (+) dalla versione della clausola WHERE di questa query, viene restituita solo una riga (la riga dove 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)