CODEWITHSUNDEEP
databaseoracle-databaseperformancejoin
Dominik Viererbe
Dominik Viererbe

Reputation: 397

Explicit JOINs vs Implicit joins?

My Database Professor told us to use:

SELECT A.a1, B.b1 FROM A, B WHERE A.a2 = B.b2;

Rather than:

SELECT A.a1, B.b1 FROM A INNER JOIN B ON A.a2 = B.b2;

Supposedly Oracle don't likes JOIN-Syntaxes, because these JOIN-syntaxes are harder to optimize than the WHERE restriction of the Cartesian Product.

I can't imagine why this should be the case. The only Performance issue could be that the parser Needs to parse a few characters more. But that is negligible in my eyes.

I found this Stack Overflow Questions:

And this sentence in a Oracle Documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

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

Can someone give me up-to-date recommendations from Oracle with link. Because she don't acknowledges StackOverflow (here can answer everyone) and the 10g Documentation is outdated in here eyes.

If i am wrong and Oracle realy don't likes JOINS now than thats also ok, but i don't find articles. I just want to know who is Right.

Thanks a lot to everyone who can help me!

Upvotes: 7

Views: 10097

Answers (4)

Lukas Eder
Lukas Eder

Reputation: 221135

There are rare occasions when the optimiser suffers from a bug when using the explicit JOIN syntax as opposed to the implicit one. For example, I once could not achieve to profit from a join elimination optimisation in Oracle 12c when using explicit joins, whereas the join was properly eliminated with the implicit join syntax. When working with views querying views querying views, lack of join elimination can indeed cause performance issues. I've explained the concept of join elimination in a blog post, here.

That was a bug (and a rare one at that, these days), and not a good reason to avoid the explicit join syntax in general. I think in current versions of Oracle, there's no reason in favour of one or the other syntax other than personal taste, when join trees are simple. With complex join trees, the explicit syntax tends to be superior, as it is more clear, and some relationships (e.g. full outer joins or joins with complex join predicates) are not possible otherwise. But neither of these arguments is about performance.

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21095

As with many non-trivial things there is no simple yes / no answer.

The first thing is, for trivial queries (as yours example in the question) it doesn’t matter which syntax you use. The classic syntax is even more compact for simple queries.

First for non-trivial queries (say more than five joins) you will learn the benefits of the ANSI syntax. The main benefit is that the join predicates are separated and divided from the WHERE condition.

Simple example – this is a complete valid query in the pre-ANSI syntax

SELECT A.a1, B.b1 
FROM A, B 
WHERE A.a1 = B.b1 and
      A.a1 = B.b1(+);

Is it inner or outer join? Furthermore if this construct is scattered in a predicate with 10 other join condition in the WHERE clause, it is even very easy to misread it.

Anyway, it would be very naïve to assume that those two syntax options are only a syntax sugar and that the resulting execution plan is for all queries, any data and all Oracle versions identical.

Yes, and there were times (about Oracle 10) you should be careful. But in times of 12 and 18 versions I do not see a reason to be defensive and I'm convinced it is safe to use the ANSI syntax from the above reason of better overview and readability.

Final remark for your professor: if you get in the position of optimizing the WHERE restriction of the Cartesian Product you typically encounters a performance problem. Make a thought experiment with a Cartesian Product of four tables with 1.000 rows each…

Upvotes: 2

Stefanos Zilellis
Stefanos Zilellis

Reputation: 611

An average sql query you will encounter in real business has 7-8 joins with 12-16 join conditions. One every 10 or 20 queries may involve nested joins or other more advanced cases. Explicit join syntax is simply far easier to maintain, debug and develop. And those factors are critical for business software - the faster and safer the better. Implicit join are somewhat easier to code if you create statements dynamically through application code. Perhaps there are other uses that i am unaware.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522386

Your professor should speak with Gordon Linoff, who is a computer science professor at Columbia University. Gordon, and most SQL enthusiasts on this site, will almost always tell you to use explicit join syntax. The reasons for this are many, including (but not limited to):

  • Explicit joins make it easy to see what the actual join logic is. Implicit joins, on the other hand, obfuscate the join logic, by spreading it out across both the FROM and WHERE clauses.
  • The ANSI 92 standard recommends using modern explicit joins, and in fact deprecated the implicit join which your professor seems to be pushing

Regarding performance, as far as I know, both versions of the query you wrote would be optimized to the same thing under the hood. You can always check the execution plans of both, but I doubt you would see a significant difference very often.

Upvotes: 17

Related Questions