2

What is the exact difference between Oracle (+) and comma separate tables and join tables. Somebody force me to use oracle (+) instead of left join. Is their any performance issue ? Because as i aware of these notions they are identical.

  • 1
    No issues, these are just the notation and (+) has been replaced after Oracle 9i with ISO 99 outer join syntax. Reference:ASKTOM. Oracle invented the outer join...more – JSapkota Aug 22 '16 at 10:44
  • 2
    @JSapkota "Oracle invented the outer join"? Really? – ypercubeᵀᴹ Aug 22 '16 at 13:15
  • 3
    Even Oracle recommends to stop using the proprietary (+) operator. Whoever is "forcing" you to use it, should read the Oracle manual. – a_horse_with_no_name Aug 22 '16 at 13:36
  • 1
    @ypercubeᵀᴹ: might actually be the case. I think outer joins have been part of Oracle since Version 2 (released in 1979) - which is long before the SQL standard existed. They were most definitely part of Oracle 6 (released 1988) – a_horse_with_no_name Aug 22 '16 at 13:48
  • 1
    @a_horse_with_no_name I still find it rather amusing that Oracle recommends that we stop using (+) when fast refresh mviews require this syntax (you can't fast refresh an mview that contains OUTER JOIN syntax, but works with (+))... one of those "do as I say, not as I do" scenario's... – Kris Johnston Aug 22 '16 at 14:23
1

Write both queries and run EXPLAIN PLAN on both of them. Comparing the plans will identify any performance differences.

From a development perspective, explicit joins offer several advantages over the older syntax.

By keeping the ON clauses together with the tables they reference, it's easier to see the join itself.

Separating out the ON clauses from the WHERE predicates make it easier to understand the developer's intention. Sometimes a spaghetti collection of tables might only have one WHERE predicate after all the join predicates are removed.

It's much harder to forget a predicate with the explicit syntax. If you forget a join predicate, you're going to return a cartesian product. Two tables with a million rows each will form a trillion-row cartesian product. If you know you've forgotten a join condition because your result set is suspicious, it's much easier to identify what's missing with the explicit syntax.

I will argue, against the advice of some longbeards, that even a large number of tables is easier to manage with explicit joins and that no query favors the implicit syntax for readability. Again, this is because the join predicates are kept next to their referenced tables. If you're limiting yourself to 80 columns of text, a long query could mean a lot of flipping up and down with the implicit syntax. If you think the typing is excessive, you should solve this with your text editor and not with sloppy syntax. (Multiline editing in Sublime Text is one solution, for example.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.