Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

February 03, 2005

ANSI Join Syntax in Oracle

I thought it was about time I dragged myself (kicking and screaming if necessary) into the late twentieth century and started using more standard SQL syntax. In particular I wanted to make my SELECT statements more portable by using the SQL 99 join syntax. As usual, the manual was worse than useless. Google came up with a few pointers though.

The first cab off the rank was this offering from the Oracle Technology Network. It is best described as brief and to the point.

Luckily, the ever reliable Mark Rittman came to my aid with this column explaining ANSI joins and why they should be used. In turn, he points to this piece at Between them they explain how to "properly" join tables together in your queries.

A common idiom that I, and I suspect many others, use isn't quite explained in any of the literature. I often need to perform two or more unrelated outer joins. For instance I want to get all of the rows from table A outer joined to any applicable rows in table B. But at the same time I also want to outer join table A to table C. In the brave old 20th Century with an Oracle database I would do this like;

SELECT a.column1, b.column1, c.column1
FROM   table_a a, table_b b, table_c.c
WHERE  a.b_key_col = b.b_key_col (+)
AND    a.c_key_col = c.c_key_col (+)

From my initial reading it didn't seem obvious how to do this with SQL-99 join syntax. The only example I could find that got close was in the dba-support article. This shows how to outer join table a to table b and then outer join table b to table c. Close, but no cigar.

After a little experimentation I found out that it's actually quite simple. To outer join table a to table b and then in the same query independently outer join table a to table c you just do something like;

SELECT a.column1, b.column1, c.column1
FROM   table_a a 
       LEFT OUTER JOIN table_b b USING (b_key_col)
       LEFT OUTER JOIN table_c c ON a.c_key_col = c.c_key_col

The query parser is smart enough to release that the second LEFT OUTER JOIN is independent of the first one and to fetch the data appropriately.

Posted by Andy Todd at February 03, 2005 02:50 PM


I am constantly amazed by Oracle. The JOIN syntax was /only/ added in Oracle 9.

As it so happens I was working on a project that needs to co-exist with MySQL 4.1 (!), and SQLServer 2000. A port to PostgreSQL was also done, but not supported commercially.

When I ported to Oracle 8, I was *amazed* by its lack of ANSI joins and reasonable DATE sub-types. And (select sysdate from dual) ? What's wrong with ... NOW() ?

Yes, I realise that we're on 11g now, but there's *plenty* of financial houses running non-critical systems on 10, 9 and 8 (and I've run into 7 more times that I'd like too).

And this costs *how* much, again?

Posted by: Aaron Brady on February 3, 2005 08:57 PM

I hadn't seen the USING clause before - thanks for the pointer. Was there a reason that you used USING for the first join, but not the second?

Posted by: Alan Green on February 4, 2005 06:24 AM

Aaron, to be fair to the people in Redwood Shores, the join syntax was only formalised as part of SQL-99, Oracle 9i was the next version of the database released after that date - I believe - and it supports the now standard syntax.

As for the reasonable DATE sub-types, why do you need them? If you want a date ignore the time component, if you want a time ignore the date component. Easy.

I believe, and again I haven't checked the party line at, that 8i and earlier aren't officially supported any more and people are highly recommended to upgrade. I certainly wouldn't port an application to anything earlier than 9i, which will co-exist quite happily with Oracle 7,8, and 10 if necessary. Oracle upgrades, if handled carefully, are usually seamless and painless.

Posted by: Andy Todd on February 4, 2005 08:10 AM

Alan, I used the using clause just to demonstrate that there are two options available.

Posted by: Andy Todd on February 4, 2005 08:10 AM

Look at the manual regarding DATE datatype.
DATE math in Oracle is so easy /powerful compared to SQLSERVER.

Posted by: Dave fowler on February 17, 2005 07:20 AM

What does the + mean?

Posted by: kathy on August 30, 2005 07:23 AM