Oracle and ANSI joins

Yesterday I was asked for my feelings about ANSI SQL join syntax. I think my main feeling is one of bafflement: why is this still an issue? After all, Oracle introduced support for the ANSI standard in SELECT statements in Oracle 9i, which means it has been available for over half my Oracle career. I admit it took me a while to get the hang of the syntax but now it is my default approach to writing queries. I like the separation of joins and filters, but I know not everybody does. For the doubters I have three words:

FULL OUTER JOIN

. To be fair, the person who asked me the question is working with a venerable codebase and the project standard is to use the old joining syntax. There is one other thing. Last year I worked on a SQL Server project. It was my first taste of T-SQL. Although overall I agree with Marvin the Paranoid Android - “How can you live in anything so small?” - I was most taken with its support for ANSI syntax in DML statements. For instance, to update one table with values from another is a simple matter:

update t1
set t1.col_a = t1.col_a + t2.col_b
from my_table t1 
     join some_other_table t2 
          on ( t1.id = t2.id )

Or to delete rows from one table if they don't match rows in another table, use an anti-join:

delete t1
from target_table t1
     left outer join check_table t2
        on ( t1.pk_col = t2.pk_col )
where t2.pk_col is null

The update is particularly elegant compared to the cumbersome sub-queries Oracle still demands. I think if Oracle had extended its ANSI SQL support beyond SELECT to the other DML commands the argument over whether to use the new syntax would have died out years ago.