Q

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


This was first published in June 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close