CODEWITHSUNDEEP
sqloracle-database
Gadziu
Gadziu

Reputation: 697

Oracle (+) operator equal number

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?

Upvotes: 0

Views: 119

Answers (3)

LingYan Meng
LingYan Meng

Reputation: 774

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;

Upvotes: 0

Hung Le
Hung Le

Reputation: 36

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.

Upvotes: 2

Serg
Serg

Reputation: 22811

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

:)

Upvotes: 0

Related Questions