Combinaciones externas de estilo Oracle en la cláusula WHERE
Para la compatibilidad con Oracle, Amazon Redshift admite el operador de combinación externo de Oracle (+) en condiciones de combinación de la cláusula WHERE. Este operador está diseñado para utilizarse exclusivamente en la definición de condiciones de combinación externa; no intente utilizarlo en otros contextos. En la mayoría de casos, otros usos de este operador se omiten silenciosamente.
Una combinación externa devuelve todas las filas que la combinación interna equivalente devolvería, además de filas no coincidentes de una o ambas tablas. En la cláusula FROM, puede especificar combinaciones externas izquierdas, derechas y completas. En la cláusula WHERE, puede especificar combinaciones externas izquierdas y derechas únicamente.
Para realizar una combinación externa en las tablas TABLE1 y TABLE2, y devolver filas no coincidentes de la TABLE1 (una combinación externa izquierda), especifique TABLE1 LEFT OUTER JOIN TABLE2 en la cláusula FROM o aplique el operador (+) en todas las columnas de combinación de la TABLE2 en la cláusula WHERE. Para todas las filas en la TABLE1 que tengan filas no coincidentes en la TABLE2, el resultado de la consulta contiene valores nulos para cualquier expresión de lista de selección que contiene columnas de la TABLE2.
Para producir el mismo comportamiento para todas las filas en la TABLE2 que no tienen filas coincidentes en la TABLE1, especifique TABLE1 RIGHT OUTER JOIN TABLE2 en la cláusula FROM o aplique el operador (+) en todas las columnas de combinación de la TABLE1 en la cláusula WHERE.
Sintaxis básica
[ WHERE {
[ table1.column1 = table2.column1(+) ]
[ table1.column1(+) = table2.column1 ]
}
La primera condición equivale a lo siguiente:
from table1 left outer join table2
on table1.column1=table2.column1
La segunda condición equivale a lo siguiente:
from table1 right outer join table2
on table1.column1=table2.column1
La sintaxis que se muestra aquí cubre el caso simple de una equijoin sobre un par de columnas de combinación. Sin embargo, también son válidos otros tipos de condiciones de comparación y varios pares de columnas de combinación.
Por ejemplo, la siguiente cláusula WHERE define una combinación externa sobre dos pares de columnas. El operador (+) debe estar adjunto a la misma tabla en ambas condiciones:
where table1.col1 > table2.col1(+)
and table1.col2 = table2.col2(+)
Notas de uso
Siempre que sea posible, use la sintaxis OUTER JOIN de la cláusula FROM estándar en lugar del operador (+) en la cláusula WHERE. Las consultas que contienen el operador (+) están sujetas a las siguientes reglas:
Solo se puede usar el operador (+) en la cláusula WHERE, y solo en referencia a las columnas de tablas o vistas.
No se puede aplicar el operador (+) a las expresiones. Sin embargo, una expresión puede contener columnas que utilicen el operador (+). Por ejemplo, la siguiente condición de combinación devuelve un error de sintaxis:
event.eventid*10(+)=category.catid
Sin embargo, la siguiente condición de combinación es válida:
event.eventid(+)*10=category.catid
No se puede usar el operador (+) en un bloque de consulta que también contiene una sintaxis de combinación de la cláusula FROM.
Si se combinan dos tablas sobre varias condiciones de combinación, debe usar el operador (+) en todas o ninguna de las siguientes condiciones. Una combinación con estilos de sintaxis dispares ejecuta una combinación interna, sin advertencia.
El operador (+) no produce una combinación externa si se combina una tabla de la consulta externa con una tabla derivada de una consulta interna.
Para usar el operador (+) para combinar de manera externa una tabla consigo misma, debe definir los alias de tabla en la cláusula FROM y hacer referencia a ellos en la condición de combinación:
select count(*)
from event a, event b
where a.eventid(+)=b.catid;
count
8798
(1 row)
No se puede combinar una condición de combinación que contiene el operador (+) con una condición OR o una condición IN. Por ejemplo:
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.
En la cláusula WHERE que combina de manera externa más de dos tablas, el operador (+) puede aplicarse solo una vez en determinada tabla. En el siguiente ejemplo, no se puede hacer referencia a la tabla SALES con el operador (+) en dos combinaciones consecutivas.
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.
Si la condición de combinación externa de la cláusula WHERE compara una columna de la TABLE2 con una constante, aplique el operador (+) a la columna. Si no se incluye el operador, se eliminan las filas combinadas de manera externa de TABLE1, que contienen valores nulos para la columna restringida. Consulte la sección de ejemplos a continuación.
EJEMPLOS
La siguiente consulta de combinación especifica una combinación externa izquierda de las tablas SALES y LISTING sobre sus columnas LISTID:
select count(*)
from sales, listing
where sales.listid = listing.listid(+);
count
172456
(1 row)
La siguiente consulta equivalente produce el mismo resultado pero utiliza la sintaxis de combinación de la cláusula FROM:
select count(*)
from sales left outer join listing on sales.listid = listing.listid;
count
172456
(1 row)
La tabla SALES no contiene registros de todas las listas de la tabla LISTING, ya que no todas las listas generan ventas. La siguiente consulta combina de manera externa SALES y LISTING, y devuelve filas de LISTING incluso cuando la tabla SALES no informa ventas para un determinado ID de lista. Las columnas PRICE y COMM, que derivan de la tabla SALES, contienen valores nulos en el conjunto de resultados para esas filas no coincidentes.
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)
Tenga en cuenta que, cuando se utiliza un operador de combinación de la cláusula WHERE, el orden de las tablas en la cláusula FROM no es importante.
Un ejemplo de una condición de combinación externa más compleja de la cláusula WHERE es el caso en el que la condición consta de una comparación entre dos columnas de tablas y una comparación con una constante:
where category.catid=event.catid(+) and eventid(+)=796;
Tenga en cuenta que el operador (+) se utiliza en dos lugares: primero, en la comparación de igualdad entre las tablas y, segundo, en la condición de comparación para la columna EVENTID. El resultado de esta sintaxis es la preservación de las filas combinadas de manera externa cuando se evalúa la restricción de EVENTID. Si elimina el operador (+) de la restricción de EVENTID, la consulta trata esta restricción como un filtro, no como parte de la condición de combinación externa. A su vez, las filas combinadas de manera externa que contienen valores nulos para EVENTID se eliminan del conjunto de resultados.
A continuación se muestra una consulta completa que ilustra este comportamiento:
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 consulta equivalente a través de la sintaxis de la cláusula FROM es de la siguiente manera:
select catname, catgroup, eventid
from category left join event
on category.catid=event.catid and eventid=796;
Si elimina el segundo operador (+) de la versión de la cláusula WHERE de esta consulta, devuelve solo 1 fila (la fila donde 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)
