Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Use ANSI SQL for Outer Join

Posted by Dylan Wan on July 4, 2008

The OLTP source applications like PeopleSoft and Siebel applications can run on many different databases including Oracle, MS SQL, or DB2. The target data warehouse can also run on different database platforms, incluidng the above databases, plus Teradata.

Various technologies can be used to enable the cross database platform support in the pre-packaged BI apps. Oracle BI Enterprise Edition allows you configure the database connections using the native drivers. ODBC can also be used to access different databases.

In order to deliver to deliver the pre-packaged ETL adapters, two technologies can be used : ANSI SQL and ETL tool specific SQL. It is preferable to avoid the dependency on the ETL plarform and use ANSI SQL92 syntax. When we move from ETL to ELT, It is important to write the portable SQL.

Before ANSI SQL was introduced, the problem with portable outer join was that the syntax was not consistent across platform. Oracle uses a (+) and SQL Server uses an asterisk =*.

Tools like Informatica, offer the platform independent syntax. When you use Informtaica join syntax, it translates the syntax to the database specific syntax, and pass the generated SQL to the database.

Now with ANSI SQL, all platforms support SQL92 standard syntax. Using the Informatica join syntax is not necessary. It increases the readability and testability if you use ANSI SQL92 for outer join.

I have being developing applications using Oracle SQL for years, The major difference for me is that the SQL92 moves the Join from the WHERE clause to the FROM clause. It is actually much easier to read.

All the articles mentioned in the References provide examples.

Here are some good guidelines:

1. If a table alias is use, you should always use the same alias rather than the table name in all places.

2. Do not use the “Using clause”. Not all platform supports that. The below example [2] using the “Using clause” I think that we should avoid.


[1] Full Outer Joins in Oracle9i

[2] Oracle PL/SQL: Performing Left Outer Joins Using SQL/92

[3] SQL Server 2005 Book Online: Using Outer Joins

[4] IBM Developer Work: A comparison of simple outer join constructs

[5] DB2 Version 4 Outer Join Basics


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s