Taking Advantage of the OUTER JOIN Syntax

By Yuli Vasiliev

Back to Oracle Database 9i, the OUTER JOIN syntax was introduced to provide the ANSI-standard alternative to proprietary outer join operator (+). However, some people still don’t want to switch. This article provides an example that illustrates how the OUTER JOIN syntax compares favorably with the (+) operator.

To walk through the sample provided in this article, you need to have access to the HR demonstration schema that is installed with every Oracle database installation by default. In particular, two HR schema tables: locations and departments will be involved in the sample join.

Suppose you want to issue a full outer join involving the locations and departments tables. An attempt to do that using the (+) operator on the both sides of the join condition will result in the following error:

select d.department_id, d.department_name,l.city from departments d, locations l where d.location_id(+) = l.location_id(+);

ERROR at line 1:
ORA-01468: a predicate may reference only one outer-joined table

As a workaround, you might use the UNION operator as follows:

select d.department_id, d.department_name,l.city from departments d, locations l where d.location_id(+) = l.location_id
UNION
select d.department_id, d.department_name,l.city from departments d, locations l where d.location_id = l.location_id(+);

It is important to note that the UNION operator eliminates duplicated rows. So, the above should generate the expected results:

DEPARTMENT_ID DEPARTMENT_NAME CITY
————- —————————— ———————
10 Administration Seattle
20 Marketing Toronto
30 Purchasing Seattle
40 Human Resources London
50 Shipping South San Francisco
60 IT Southlake
70 Public Relations Munich
80 Sales Oxford
90 Executive Seattle
100 Finance Seattle
110 Accounting Seattle
120 Treasury Seattle
130 Corporate Tax Seattle
140 Control And Credit Seattle
150 Shareholder Services Seattle
160 Benefits Seattle
170 Manufacturing Seattle
180 Construction Seattle
190 Contracting Seattle
200 Operations Seattle
210 IT Support Seattle
220 NOC Seattle
230 IT Helpdesk Seattle
240 Government Sales Seattle
250 Retail Sales Seattle
260 Recruiting Seattle
270 Payroll Seattle
Beijing
Bern
Bombay
Geneva
Hiroshima
Mexico City
Roma
Sao Paulo
Singapore
South Brunswick
Stretford
Sydney
Tokyo
Utrecht
Venice
Whitehorse

However, using the OUTER JOIN syntax provides a more straightforward and elegant way to achieve the same result:

select d.department_id, d.department_name, l.city from departments d FULL OUTER JOIN locations l ON d.location_id = l.location_id;

The above should produce the same results as the preceding query did.

About the Author

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, and service-oriented architecture (SOA). He is the author of Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting (Packt, 2010) and PHP Oracle Web Development (Packt, 2007) as well as a series of other books on the Oracle technology.