Dailycode.info

Short solution for short problems

Outer join in Oracle SQL

I'm by origin a MS SQL  writer. This is the SQL I grew up with, although at school I learned Oracle, at my first job it was MS SQL and since then 90 %  of the SQL I use was MQ SQL. But at my current employer, it the other way around. It's 90% Oracle. Working more then 1 year with Oracle, I'm becoming more and more aware of the differences between MS SQL en Oracle SQL. I'm will discuss one of these differences.

When you need information from 2 tables, in MS SQL we use the Join clause, inner or outer. When it's possible that the child table has no related data for the record of the master table in the query, and still you want to see the record from the master table, you will need to use outer join. Now I tried this in Oracle SQL, there was no syntax error, but also no data. I could figure out what I was doing wrong.

This syntax returned nothing:

SELECT a.GUID, a.ROLE_NAME, b.APP_NAME, b.guid as APPGUID 
FROM CIG_MGMT.ROLE a
LEFT OUTER JOIN CIG_MGMT.ROLE_APPS b ON b.ROLE_GUID = a.GUID 
WHERE a.IS_VALID = 1 AND b.IS_VALID = 1 
ORDER BY a.ROLE_NAME,b.APP_NAME

So I started looking for an answer. I came up with this site: http://www.adp-gmbh.ch/ora/sql/outer_join.html

I changed the code into this and it worked!

SELECT a.GUID, a.ROLE_NAME, b.APP_NAME, b.guid as APPGUID 
FROM CIG_MGMT.ROLE a, CIG_MGMT.ROLE_APPS b 
WHERE b.ROLE_GUID(+) = a.GUID and 
a.IS_VALID = 1 AND b.IS_VALID(+) = 1 
ORDER BY a.ROLE_NAME,b.APP_NAME
 

Just use the (+) syntax for every field of the (child) table in the where clause. 

A solution was found, but not a reason why the left outer join wouldn't work and the (+) worked fine. The first reason could be that I was using Oracle 8i wich only supports the (+). But this is not the case. We are using 9i and the compiler doesn't complain to the syntax.

So I'm still looking for an explanation, because I have very little time, I cannot investigate this and I will just implement the (+) solution. I hope somebody who knows the answer takes the time to post it here... Or maybe in the future when I will have some more time, I will remember this and look for an answer. But this first 2 years will be hectic ;-)