What does using the (+) in an Oracle query mean? [double]
I would like to know the use of the (+) operator in the following query and what is its possible equivalence in MySQL.
SELECT 'E' tipo_envase, DECODE(NVL(pasi_parametro1,'-'),'-','CRE','CON') tipo_pago, vaca_serie, vaca_numero, vaca_loca_clave, vaca_clnt_numero, vaca_conv_numero, vaca_locl_clave,
vaca_loco_clave, vaca_pedi_serie, vaca_pedi_numero, vaca_plbo_clave, vaca_toneladas, vaca_comentarios, pedi_tipa_clave, pedi_prod_clave, pedi_mone_clave, pedi_tipe_clave,
pedi_plbo_stock
FROM scc_vales_carga, scc_pedidos, scc_productos, scc_parametros_sistema
WHERE vaca_status = 'E'
AND vaca_tavi_numero = pw_tarjeta
AND vaca_eqpr_fecha = pw_fecha_tarjeta
AND pedi_serie = vaca_pedi_serie
AND pedi_numero = vaca_pedi_numero
AND prod_clave = pedi_prod_clave
AND pasi_sist_clave (+) = 'SCC'
AND pasi_planta_bodega (+) = '001'
AND pasi_parametro (+) = 40
AND pasi_parametro1 (+) = pedi_tipa_clave
AND NOT EXISTS (SELECT 'X'
FROM scc_parametros_sistema
WHERE pasi_sist_clave = 'SCC'
AND pasi_planta_bodega = '001'
AND pasi_parametro = 38
AND pasi_parametro1 = prod_prpr_clave)
ORDER BY tipo_envase, tipo_pago, vaca_serie, vaca_numero;
This is in Oracle.
2 answers
Is the old syntax for expressing an outer join.
If you look, the FROM clause lists all tables, and the conditions for joining them are in the where.
select a.columnaA, b.columnaB
from a, b
where a.llave (+) = b.Llave
Is equivalent to
select a.columnaA, b.columnaB
from a
left outer join b on a.Llave = b.Llave
Then
select a.columnaA, b.columnaB
from a, b
where a.llave = (+) b.Llave
Is equivalent to
select a.columnaA, b.columnaB
from a
right outer join b on a.Llave = b.Llave
And finally
select a.columnaA, b.columnaB
from a, b
where a.llave (+) = (+) b.Llave
Is equivalent to
select a.columnaA, b.columnaB
from a
full outer join b on a.Llave = b.Llave
The queries you're seeing were written in Oracle time immemorial.
[edit] To write the equivalent mySQL query, it would be necessary to know the structure of the tables, because the fields are not qualified in the original query, but with that said, that part remains as an exercise for the OP.
Although the general idea of the answer given by @ jachguate is correct, almost all the specific details in his explanation are incorrect.
Below I present the corrections for the benefit of future readers:
Example #1
select a.columnaA, b.columnaB
from a, b
where a.llave (+) = b.Llave
According to @ jachguate, it is equivalent to:
select a.columnaA, b.columnaB
from a
left outer join b on a.Llave = b.Llave
... this is wrong and the other way around. Rather, it is equivalent to:
select a.columnaA, b.columnaB
from a
right outer join b on a.Llave = b.Llave
... or equivalent a:
select a.columnA, b.columnB
from b
left outer join a on a.Llave = b.Llave
Example #2
select a.columnaA, b.columnaB
from a, b
where a.llave = (+) b.Llave
According to @ jachguate, it is equivalent to:
select a.columnaA, b.columnaB
from a
right outer join b on a.Llave = b.Llave
... again, this is wrong. First, because the query is not even valid, because the (+) symbol is not in the right place. He probably meant:
select a.columnaA, b.columnaB
from a, b
where a.llave = b.Llave (+)
... but even so, the equivalent is not correct. Rather it is:
select a.columnA, b.columnB
from a
left outer join b on a.Llave = b.Llave
... or also:
select a.columnA, b.columnB
from b
right outer join a on a.Llave = b.Llave
Example #3
select a.columnaA, b.columnaB
from a, b
where a.llave (+) = (+) b.Llave
According to @ jachguate equivalent to:
select a.columnaA, b.columnaB
from a
full outer join b on a.Llave = b.Llave
... it is also incorrect, for 2 reasons:
- as in the example above,
(+) b.Llaveis invalid. The symbol(+)should be after the column, not before (example:b.Llave (+). - There is no way to express a
full outer joinusing the(+)symbol. Trying to do so gets the error:a predicate may reference only one outer-joined table.