I've been working on report definitions lately. The work involves writing various database queries which are the data source that feed the reports.
One of our requirements is that the reports are to be able to run on an Oracle or SQL Server database. So I'm keen to use common SQL that will work on both DB platforms as much as possible in order to avoid having to maintain separate Oracle and SQL Server queries.
Some of the reports require outer joins. In the past this would have required separate SQL. Oracle used an awkward (+) notation to indicate outer joins. SQL Server uses the more standard OUTER JOIN syntax.
I'd kind of resigned myself to this but then I remembered that Oracle now in 9 and later now supports the ANSI OUTER JOIN style for indicating outer joins. This is great and I was able to do my queries using outer joins reusing the same SQL for Oracle and SQL Server without requiring separate DB specific syntax. Thank you Oracle for adding this.
One thing I'd been a bit suspicious of with OUTER JOIN was that it seemed hard for me to grasp what was going on with how the tables were joined. There were joins going on in the FROM section as well as standard joins down in the WHERE clauses. I found it hard to grasp this.
But then I kind of had a eureka moment when I realised that in the FROM clause the OUTER JOINs were creating a virtual table off the primary table which was outer joined to the other tables. So then everything made sense to me thinking about the OUTER JOIN clauses as forming a single virtual table which was then joined and evaluated in the usual way down below in the WHERE clause.
Of course all of an SQL query is about forming a virtual table which is the query results but once I was able to visualize what was happening in the FROM clause the OUTER JOIN now seems quite natural and intuitive. I hope I won't have to ever go back to trying to remember which side to put the (+) on.
