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 dbasupport.com. 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

Advertisements