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  using the “Using clause” I think that we should avoid.