1

I have some very old Oracle SQL code I need to review, as per below and am trying to understand what the (+) operator is doing in the where clause after the first use of it

select *
     from table_a a,
          table b b
    where 
         a.id = b.id (+)
         and b.seq_nb (+) = 1
         and b.type_cd (+) = 'DOLLR'

I thought (+) was a outer join equivalent, so

 from table_a a,
              table b b
        where 
             a.id = b.id (+)

would be the same as

from table a a left outer join table b b on a.id=b.id

so how can you have outer joins to hard coded variables as below?

     b.seq_nb (+) = 1
     and b.type_cd (+) = 'DOLLR'

Any help would be greatly appreciated, thank you!

MPT
  • 25
  • 4
  • Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Jul 24 '20 at 00:47
  • Does this answer your question? [Difference between Oracle's plus (+) notation and ansi JOIN notation?](https://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation) – philipxy Jul 24 '20 at 00:47
  • LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. ON takes any condition, not just equality of two columns. (+) can express certain cases of ON. Always read the manual & an introduction for functionality you are using, let alone functionality you have a problem with. – philipxy Jul 24 '20 at 00:48

1 Answers1

2

It's the same as:

select *
from table_a a
   left outer join table_b b 
     on a.id = b.id 
    and b.type_cd = 'DOLLR'
    and b.seq_nb = 1

Sometimes also referred to as a "filtered outer join".

It is equivalent to an outer join with a derived table:

select *
from table_a a
   left outer join (
     select * 
     from table_b
     where b.type_cd = 'DOLLR'
       and b.seq_nb = 1
   ) b on a.id = b.id