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' )


// 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

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.