Oracle (+) operator equal number

Question

I know what (+) operator does in this query:

SELECT 
   * 
FROM
   TBL_TEST t, TBL_TEST_2 t2
WHERE
   t.FIELD (+) = t2.FIELD;

But what (+) operator does in such query as:

SELECT 
   * 
FROM
   TBL_TEST t
WHERE
   t.FIELD (+) = 2;

I don't know...

Can someone explain it?

[duplicate question](https://stackoverflow.com/questions/430274/oracle-what-does-do-in-a-where-clause) — user2877959, Aug 18 '17 at 09:02
@user2877959 I don't think it's duplicate... I know what (+) do in joins. I edit my question for better understanding my question — Gadziu, Aug 18 '17 at 09:04
Possible duplicate of [Oracle: What does \`(+)\` do in a WHERE clause?](https://stackoverflow.com/questions/430274/oracle-what-does-do-in-a-where-clause) — user2877959, Aug 18 '17 at 09:04
This the legacy syntax for denoting an out join. Without a join this makes no sense, even if you can write it. — Mithrandir, Aug 18 '17 at 09:06
@Gadziu it's still a left join. It just doesn't make any sense — user2877959, Aug 18 '17 at 09:06
Ok, I think the same... I review code for someone else, and I saw somethign like this and it doesn't make sanse... — Gadziu, Aug 18 '17 at 09:09

score 2 · Accepted Answer · answered Aug 18 '17 at 09:27

I have an example

    select * from xxtest
V_ID    V_NAME  V_ADDRESS
5   PriceList   349FDAFD34M
7   PriceList   349FDAFD34M
7   Footer1 349FDAFD34M
5   Footer1 349FDAFD34M
5   Header1 349FDAFD34M
7   Header1 349FDAFD34M




 select * from xxtest2
V_ID    V_NAME  V_ADDRESS
7   Header1 349FDAFD34M

query 01

    select * 
    from xxtest aa, xxtest2 bb 
    where aa.v_id = bb.v_id(+) and bb.v_id(+) = 7
V_ID    V_NAME  V_ADDRESS   V_ID    V_NAME  V_ADDRESS
7   PriceList   349FDAFD34M 7   Header1 349FDAFD34M
7   Footer1 349FDAFD34M 7   Header1 349FDAFD34M
7   Header1 349FDAFD34M 7   Header1 349FDAFD34M
5   PriceList   349FDAFD34M  -   -   - 
5   Footer1 349FDAFD34M  -   -   - 
5   Header1 349FDAFD34M  -   -   -

query 02

    select * 
    from xxtest aa, xxtest2 bb 
    where aa.v_id = bb.v_id(+) and bb.v_id = 7
V_ID    V_NAME  V_ADDRESS   V_ID    V_NAME  V_ADDRESS
7   PriceList   349FDAFD34M 7   Header1 349FDAFD34M
7   Footer1 349FDAFD34M 7   Header1 349FDAFD34M
7   Header1 349FDAFD34M 7   Header1 349FDAFD34M

Hope it's will help u.

Thank's a lot:) So it have kind of sanse now :D – Gadziu Aug 18 '17 at 09:42 — Gadziu, Aug 18 '17 at 09:42

score 0 · Answer 2 · answered Aug 18 '17 at 09:23

0

To force Oracle to confess it's outer join

select *
from ( select 1 n from dual
  ) t
where n (+) = n+1

ORA-01416: two tables cannot be outer-joined to each other

:)

answered Aug 18 '17 at 09:23

Serg

22,285
5
21
48

score 0 · Answer 3 · answered Aug 18 '17 at 09:29

I tested it in Oracle 12G, and it returns the same result as:

SELECT 
   * 
FROM
   TBL_TEST t
WHERE
   t.FIELD = 2;

My guess for the original code is trying to say if the field eques 2, return the field value, otherwise return null. Something like below:

SELECT T.STUDENT, T2.*
  FROM TBL_TEST T
       LEFT OUTER JOIN (SELECT 2 COLU FROM DUAL) T2 ON T.COUNT = T2.COLU;

Oracle (+) operator equal number

3 Answers3