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
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 JoinsThis 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 RewriteCurrently 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.