Ask the Expert

Oracle and Sybase outer joins

I am trying to get an Oracle join to work as my Sybase join. The Sybase join returns all results and pads with nulls. The Oracle join doesn't seem to function properly and will not pad the results with nulls. This is operating in Powerbuilder 6.5. Syntax for both are below:

//// ORACLE DRAW
SELECT FAST.CONNECTION.CONNECT_ID,   
       FAST.CONNECTION.DESCRIPTION,   
       FAST.ACCOUNT_SU.SSO_LOGIN_ID  
FROM   FAST.CONNECTION,   
       FAST.ACCOUNT_SU  
WHERE ( FAST.account_su.connect_id 
      = FAST.connection.connect_id ) 
  and (( FAST.account_su.sso_login_id = 'rsmyer' ))
  AND ( FAST.connection.active_ind = 'y' )


//

    Requires Free Membership to View

    Login 

 SYBASE DRAW
SELECT dbo.connection.connect_id,
       dbo.connection.description,
       dbo.account.sso_login_id     
FROM   dbo.connection,
       dbo.account     
WHERE ( dbo.connection.connect_id 
     *= dbo.account.connect_id ) 
  and (( dbo.account.sso_login_id = 'rsmyer' )) 
  and ( dbo.connection.active_ind = 'y' )

The "*=" in the Sybase WHERE clause means that it's a left outer join, which returns all rows of the connection table, with nulls in sso_login_id for unmatched rows.

A left outer join in Oracle actually has similar syntax, except that it uses the outer join operator "(+)" instead of an asterisk beside the equal sign. I'll remove the parentheses in your WHERE clause to make the Oracle outer join operator more noticeable --

SELECT FAST.CONNECTION.CONNECT_ID,   
       FAST.CONNECTION.DESCRIPTION,   
       FAST.ACCOUNT_SU.SSO_LOGIN_ID  
FROM   FAST.CONNECTION,
       FAST.ACCOUNT_SU  
WHERE  FAST.account_su.connect_id 
     = FAST.connection.connect_id (+)
  and  FAST.account_su.sso_login_id = 'rsmyer' 
  AND  FAST.connection.active_ind = 'y'

Place the outer join operator "(+)" behind the columns of the table that will receive the nulls.

Actually, you should probably be using SQL standard LEFT OUTER JOIN syntax.

SELECT FAST.CONNECTION.CONNECT_ID,   
       FAST.CONNECTION.DESCRIPTION,   
       FAST.ACCOUNT_SU.SSO_LOGIN_ID  
FROM   FAST.CONNECTION
 LEFT OUTER JOIN   
       FAST.ACCOUNT_SU  
    ON FAST.account_su.connect_id 
      = FAST.connection.connect_id 
WHERE  FAST.account_su.sso_login_id = 'rsmyer' 
  AND  FAST.connection.active_ind = 'y'

Whether Powerbuilder supports this syntax, I do not know -- you'll have to check that out yourself. But both Sybase and Oracle do.

For More Information


This was first published in June 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top