Oracle outer join short hand with upper

Question

Works:

AND UPPER(a.name) = b.lname(+)

does not work

AND UPPER(a.name) = UPPER(b.lname) (+)

Moving to ANSI joins is an option but a painstaking one. This code should be changed in lot of places and there are lot of joins. I would like to get this syntax correct and be on my way.

Is it possible?

Answer 1

It is quite possible that the second version of the code will not work, ever. If it wasn't in use before, it is all the more plausible that it won't work. (See the accepted answer for how to use the obsolesent notation. I still think that the rest of the advice below stands - but note carefully the qualifier 'when you need to modify the SQL'; if you don't need to change the SQL for some other reason, there's no necessity to remove the old-style notation.)

Bite the bullet and deal with the ANSI join when you need to do the case-insensitive comparison. Or investigate a locale-based alternative (with case-insensitive comparisons), if such an option exists in Oracle.

Fundamentally, though, you should consign the old '(+)' outer join notation to the trash can. When you have to modify an SQL statement, remove the old (obsolescent) notation and use the ANSI join notation instead.

---

A comment asks 'how can this be converted to ANSI'?

You rewrite the FROM clause as well as the WHERE clause - often moving join conditions from the WHERE clause to the ON conditions in the FROM clause.

 SELECT a.*, b.*
   FROM a LEFT OUTER JOIN b ON UPPER(a.name) = UPPER(b.lname)

---

Another comment asks 'how to extend the join to three tables'?

 SELECT a.*, b.*
   FROM a 
     LEFT OUTER JOIN b ON UPPER(a.name) = UPPER(b.lname)
     LEFT OUTER JOIN c ON on a.first = c.first

Answer 2

Yuckiness aside, incorporating UPPER() with the old skool OUTER JOIN syntax is simplicity itself: we just need to get the brackets in the right order:

SQL> select t23.name
  2         , d.university
  3  from t23
  4       , t_doctors d
  5  where upper(t23.name) = upper(d.name(+))
  6  order by t23.id
  7  /

NAME         UNIVERSITY
------------ --------------------
SAM-I-AM
MR KNOX
FOX IN SOCKS
CAT
LORAX
BILLY
MAISIE
DR SINATRA   Whoville U
DR FONZ      U of Grin-itch
PINNER BLINN

10 rows selected.

SQL> 

Here is how to deploy the newer syntax with multiple tables:

SQL> select t23.name
  2         , d.university
  3         , k.school
  4  from t23
  5       left outer join t_doctors d
  6                  on upper(t23.name) = upper(d.name)
  7       left outer join t_kids k
  8                  on upper(t23.name) = upper(k.name)
  9  order by t23.id
 10  /

NAME         UNIVERSITY           SCHOOL
------------ -------------------- --------------------
SAM-I-AM                          Mulberry St Junior
MR KNOX
FOX IN SOCKS
CAT
LORAX
BILLY                             Roover River High
MAISIE                            Roover River High
DR SINATRA   Whoville U
DR FONZ      U of Grin-itch
PINNER BLINN

10 rows selected.

SQL>