Answer

Alternative to LEFT OUTER JOIN?

Rudy Limeback
By
Published: 18 Apr 2005

Is there any other alternative for the given query? I don't want to user left outer join in the query. It is giving me some problem on the developement side of my project.

SELECT 
A.USER_ID,A.FK_ROLE_ID,B.FIRST_NM,
B.LAST_NM,B.ADD_LINE_1,B.ADD_LINE_2,
B.STATE,B.CITY,B.ZIP,B.PK_PARENT_ID,C.ROLE_NM 
FROM CHATHAM_USERS A LEFT OUTER JOIN PARENT B 
ON A.FK_PARENT_ID=B.PK_PARENT_ID,ROLE C 
WHERE A.FK_ROLE_ID=C.PK_ROLE_ID AND
C.PK_ROLE_ID IN (1,2,3) --AND
--B.ACTV_REC_IN = 'Y'

The first thing to notice is that there might be a problem in how the query executes (depending on which database system you're using). You're mixing JOIN syntax with the "old school" table list syntax which uses a comma-separated list of tables in the FROM clause and join conditions in the WHERE clause.

Let's rewrite the query using only JOIN syntax:

select A.USER_ID
     , A.FK_ROLE_ID
     , B.FIRST_NM
     , B.LAST_NM
     , B.ADD_LINE_1
     , B.ADD_LINE_2
     , B.STATE
     , B.CITY
     , B.ZIP
     , B.PK_PARENT_ID
     , C.ROLE_NM 
  from CHATHAM_USERS A 
inner
  join ROLE C 
    on A.FK_ROLE_ID = C.PK_ROLE_ID 
   and C.PK_ROLE_ID IN (1,2,3)  
left outer 
  join PARENT B 
    on A.FK_PARENT_ID
     = B.PK_PARENT_ID

Now, let's address your original question. You want to avoid using a LEFT OUTER JOIN. To be honest, I cannot see why. But to answer your question, there is a way to achieve the same results, by using a UNION query of an INNER join and a NOT EXISTS subquery.

select A.USER_ID
     , A.FK_ROLE_ID
     , B.FIRST_NM
     , B.LAST_NM
     , B.ADD_LINE_1
     , B.ADD_LINE_2
     , B.STATE
     , B.CITY
     , B.ZIP
     , B.PK_PARENT_ID
     , C.ROLE_NM 
  from CHATHAM_USERS A 
inner
  join ROLE C 
    on A.FK_ROLE_ID = C.PK_ROLE_ID 
   and C.PK_ROLE_ID IN (1,2,3)  
inner

  join PARENT B 
    on A.FK_PARENT_ID
     = B.PK_PARENT_ID
union all     
select A.USER_ID
     , A.FK_ROLE_ID
     , null
     , null
     , null
     , null
     , null
     , null
     , null
     , null
     , C.ROLE_NM 
  from CHATHAM_USERS A 
inner
  join ROLE C 
    on A.FK_ROLE_ID = C.PK_ROLE_ID 
   and C.PK_ROLE_ID IN (1,2,3)  
 where not exists
     ( select *
         from PARENT B 
        where PK_PARENT_ID
            = A.FK_PARENT_ID )

Why would someone want to use the UNION and NOT EXISTS instead of a LEFT OUTER JOIN? No idea.

But wait -- there's more.

In your original query, there's a condition on the B table that is commented out. Is this the real source of your problems? When you uncomment that condition in the WHERE clause, guess what happens -- you no longer return any unmatched rows, correct? That's because in a LEFT OUTER JOIN, conditions on the right table (the B table in this case) must be placed in the ON clause of the join, and not the WHERE clause.

select A.USER_ID
     , A.FK_ROLE_ID
     , B.FIRST_NM
     , B.LAST_NM
     , B.ADD_LINE_1
     , B.ADD_LINE_2
     , B.STATE
     , B.CITY
     , B.ZIP
     , B.PK_PARENT_ID
     , C.ROLE_NM 
  from CHATHAM_USERS A 
inner
  join ROLE C 
    on A.FK_ROLE_ID = C.PK_ROLE_ID 
   and C.PK_ROLE_ID IN (1,2,3)  
left outer 
  join PARENT B 
    on A.FK_PARENT_ID
     = B.PK_PARENT_ID
   and B.ACTV_REC_IN = 'Y'

If you do not understand the difference -- yes, it's subtle -- between a LEFT OUTER JOIN ON condition and a WHERE condition, please post another question and I'll explain it.

Next Steps

Find out if there's a difference between a LEFT JOIN and LEFT OUTER JOIN

Dig Deeper on Oracle development languages