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.