What does = = + mean in an Oracle query

Question

Usually in the C ++ programming language, plus means adding in the example below

int x; x += 1; 

However, in the plsql request, I am confused by the same use. This use does not mean add. In this case, what's the point = +?

 Select c.* From alf_numeric a, run_of_id b, tail_of_st c WHERE category_id IN(33,36) AND a.flow_id =+ b.flow_id 

Any idea?

Answer 1

It:

 ... FROM alf_numeric a, run_of_id b WHERE a.flow_id = b.flow_id (+) 

would mean:

 ... FROM alf_numeric a LEFT JOIN run_of_id b ON a.flow_id = b.flow_id 

---

I suppose that:

 a.flow_id =+b.flow_id 

parsed as (simple):

 a.flow_id = (+b.flow_id) 

and same as:

 a.flow_id = b.flow_id 

Answer 2

It seems to me that the "+" part of "= +" is a non-op. Try the following statements:

 CREATE TABLE test1 (v1 NUMBER); INSERT INTO test1(v1) VALUES (-1); INSERT INTO test1(v1) VALUES (1); CREATE TABLE test2(v2 NUMBER); INSERT INTO test2(v2) VALUES (-1); INSERT INTO test2(v2) VALUES (1); SELECT * FROM test1 t1 INNER JOIN test2 t2 ON (t1.v1 = t2.v2) WHERE t1.v1 =+ t2.v2; 

which returns

 V1 V2 -1 -1 1 1 

Thus, it seems that the "+" operator does nothing, it just answers everything that is. As a test, follow these instructions:

 SELECT V1, +V1 AS PLUS_V1, ABS(V1) AS ABS_V1, -V1 AS NEG_V1 FROM TEST1; 

and you will find that it returns

 V1 PLUS_V1 ABS_V1 NEG_V1 -1 -1 1 1 1 1 1 -1 

which seems to confirm that unary + is effectively non-op.

Share and enjoy.

Answer 3

In a SELECT statement, a clause

 a.flow_id =+b.flow_id 

- this is basically a comparison. It checks to see if a.flow_id b.flow_id . Thus, the + operator in this case is an arithmetic operator working with one operand. It turns the sign of value into positive.

Update:

I think I was a little mistaken. The operator does not change sign. This has no effect.

Answer 4

This is probably a typo for the old Sybase left join syntax, which would be =* instead of =+ . If so, you can rewrite the query more clearly using join s, for example:

 select c.* From alf_numeric a left join run_of_id b on a.flow_id = b.flow_id cross join tail_of_st c WHERE category_id IN(33,36) 

That would, in principle, return the entire tail_of_st table for each entry in alf_numeric , with a filter on category_id (not sure which table is in it). Mysterious request!

Answer 5

In your C ++ example, denotes a positive sign, it has nothing to do with the addition. Just as you can write x = -1 , you can also write x = +1 (which is x = 1 , since + as the sign can be omitted - and, in most cases, since it does not actually have effect whatever). But both of these cases are an appointment in C ++, and not an addition - the actual calculation is not performed; you are probably thinking of x += 1 (order is important!), which will increase x by 1.

In your SQL query, I think + should have a special meaning - probably indicating an outer join . Although, if I read this document correctly, it should be a.flow_id = b.flow_id (+) ; as here, I doubt that the query parser recognizes it as an external join, but instead just interprets it as a positive sign, as in your C ++ example.

Answer 6

I believe the syntax of the connection. The standard way is to say something like tableA join tableB on <whatever> , but some databases, such as Sybase and Oracle, support alternative syntax. In Sybase, this is =* or *= . Postgres probably does the same. From the format, I would assume the right outer join, but it's hard to say. I looked in the PG docs but didn’t see at once.

By the way, in C you will have x += 1 not x = +1 .