What does the use of (+) mean in an Oracle query? [duplicate]

Question

What does the use of (+) mean in an Oracle query? [duplicate]

Navigation

#1 by (5 votes)
#2 by (2 votes)

3

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.

mysql sql oracle

asked by Osiel Candido Onofre 04.10.2016 в 00:05

source

2 answers

2

Although the general idea of the answer given by @jachguate is correct, almost all the specific details in his explanation are incorrect.

Below are 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 incorrect and revealing. Rather, it is equivalent to:

select a.columnaA, b.columnaB
  from a 
 right outer join  b on a.Llave = b.Llave

... or also equivalent to:

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 incorrect. First, because the query is not even valid, because the symbol (+) is not in the right place. Probably wanted to say:

select a.columnaA, b.columnaB
  from a, b
 where a.llave = b.Llave (+)

... but even so, the equivalent is not correct. It is rather:

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 is equivalent to:

select a.columnaA, b.columnaB
  from a 
       full outer join  b on a.Llave = b.Llave

... is also incorrect, for 2 reasons:

As in the previous example, (+) b.Llave is not valid. The (+) symbol must be after the column, not before (example: b.Llave (+) .

There is no way to express a full outer join using the symbol (+) . Trying to do it gets the error: a predicate may reference only one outer-joined table .

answered by 16.10.2016 в 16:39

Sort a List of Objects in Java Problem with struct in C ++

score 5 · Accepted Answer

It's the old syntax to express an outer join.

If you notice, the clause from list all the tables, and the conditions to join 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 are seeing were written in time immemorial by Oracle.

[edit] To write the equivalent mySQL query, it would be necessary to know the structure of the tables, since the fields are not qualified in the original query, but with that said, that part remains as an exercise for the OP.