[RESOLVED] (+) in Sql

[fofa4ever]

Good Day;

I've a simple question :

What the (+) is using for ?

select * from edge.CLIENT C, edge.SOURCE S,EDGE.AGING_OFF E
where c.salesman_code = s.source_code (+) and
C.cust_id = E.ldg_id and
E.ldg_type = C.supp_yn and
E.CREATION_DATE BETWEEN '1-MAY-2011' AND '2-MAY-2011'

Thank you

[si_the_geek]

(+) is not standard SQL

If memory serves it is specific to Oracle, and means a Left Outer join or Right Outer join (depending on exactly where it is).

[Hack]

(+) is not standard SQL

It is not standard T-SQL, but it is standard PL/SQL

If memory serves it is specific to Oracle, and means a Left Outer join or Right Outer join (depending on exactly where it is).

You are correct, and it confused the ever lovin' bejesus out of me when I did my very first project with Oracle as the back end.

It is backwards to what you think you would do for LEFT and RIGHT outer joins.

It is difficult to explain...

When you want to code a Left Outer Join, you put the (+) sign on the table that satisfies the "right" table join condition

When you want to code a Right Outer Join, you put the (+) sign on the table that satisfies the "left" table join condition

To this day, that doesn't make sense to me (althought I understand it (I think)).

Having said that I didn't use it and explicity said "RIGHT OUTER JOIN" or "LEFT OUTER JOIN" in my queries.

[leinad31]

I'm just guessing but it's probable related to tree view control: (+) to expand, (-) to collapse

The table column in the join with the (+) is the one that can be thought of as "expandable" into zero or multiple rows when related to one row of the other table.

So for condition WHERE tblA.ID = tblB.FK_ID(+), it is tblB that determines the leaf nodes (or rows returned) using the tree view analogy (you would click the (+) to view the leaf nodes in a tree view control).

I hope that makes it easier to remember for everyone; it works for me.

[RhinoBull]

As already pointed out in Oracle "+" stands for outer join:

Left outer join:
where c.salesman_code = s.source_code (+)

Right outer join:
where c.salesman_code (+) = s.source_code

I personally prefer this syntax to one that's more explicit which requires more typing and imho is quite cumbersome.

[fofa4ever]

Thanx everyone, now it is clear.