Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Francesco.

Asked: July 25, 2017 - 9:55 am UTC

Last updated: July 25, 2017 - 2:20 pm UTC

Version: 9+

Viewed 1000+ times

You Asked

Dear Oracle Masters,

here is a poor disciple looking for guidance, I know the way to reach the true knowledge does not have an end, but I would appreciate few words to make my journey more safe, especially for my fellow travelers.

Here is my question, we are already in the second half of year 2017 and I still meet people who think it is a good idea to write joins like:

SELECT t1.c1,
       t2.c2
  FROM t1, t2 
 WHERE t1.c1 = t2.c1
   AND t1.c2 = 'whatever';


instead of

    SELECT t1.c1,
           t2.c2
      FROM t1
INNER JOIN t2 
        ON t1.c1 = t2.c1
     WHERE t1.c2 = 'whatever';


I see the advantages of the second syntax, even without playing with outer joins, and it is easy to explain what are the benefits. Often the answer I get is that the first one is the Oracle "recommended" syntax. Here is my struggle, because as another wise master once said:

"Misfortune comes from one’s mouth and ruins one, but fortune comes from one’s heart and makes one worthy of respect"

It will be of great comfort for me to have few words from you on this subject and an URL for me to point out to other people going in the same direction.

Thanks,
Mucio

and Chris said...

Oracle style joins may have been recommended in the past, but the current guidance is:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator

http://docs.oracle.com/database/122/SQLRF/Joins.htm#SQLRF30046

This is because there are number of restrictions that apply to the (+) operator, but not the "outer join" clause. Follow the link for the full list.

That said, which you use is largely a matter of style. In most cases it doesn't matter which you use so it comes down to personal preference. But there are some other situations which may force you one way or the other.

Full Outer Joins

This is nice and easy with ANSI syntax:

select * from t1
full outer join t2
on t1.join_col = t2.join_col


But a cumbersome query that accesses your tables twice with Oracle style:

select * from t1, t2
where  t1.join_col = t2.join_col (+) 
union all
select * from t1, t2
where  t1.join_col (+) = t2.join_col
and    t2.join_col is null;


Materialized View Query Rewrite

Currently ANSI syntax isn't fully supported for query rewrite. So if you use MVs a lot you're better off sticking with Oracle style.

See the full analysis from Dani Schnider at:

https://danischnider.wordpress.com/2016/11/30/ansi-join-syntax-and-query-rewrite/

But these are both rare cases, so unlikely to force your decision. It's possible you may find some other edge cases that push you one way or another.

Personally I prefer ANSI style. But I think it's more important for your code to be consistent. This makes it easier to follow. If working on a legacy app all coded using Oracle syntax, I'd use that.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What does Oracle do?

stephan uzzell, July 25, 2017 - 11:32 am UTC

https://jonathanlewis.wordpress.com/2016/01/04/ansi-bug/

Jonathan Lewis claimed (at least as of January last year) that Oracle silently translated ANSI syntax into its own internal syntax before executing.

If that's the case, then isn't it by default better to write Oracle-syntax SQL? That avoids any translation which may or may not be exactly equivalent....
Chris Saxon
July 25, 2017 - 2:17 pm UTC

Yes, ANSI is translated to Oracle syntax. But if you find a translation that is not exactly equivalent that's a bug and you should raise it with support.

Good, but not enough

Francesco Mucio, July 25, 2017 - 11:59 am UTC

We cannot reduce everything to personal preference.

While we have also clear benefits (beside the fact that outer joining cannot be done properly with multiple tables):

- Separate join conditions from where conditions, easier to understand and to maintain.
- Avoid accidental cross join (if you forget the join condition).
- The syntax is standard in all SQL engines (it's not the case for the Oracle outer).

The fact that the Query Rewrite doesn't work with Materialized Views written with pre-ANSI 92 syntax is a bug that should be fixed.
Chris Saxon
July 25, 2017 - 2:20 pm UTC

And people have arguments for using Oracle syntax such as less typing. Not everything has a pure scientific argument :)

Yep, the MV issue should be fixed. But that doesn't help you if you need it now.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.