What is the difference between join in FROM clause and WHERE clause?

Question

We have a Oracle 10g and most of our applications are running Oracle Forms 6i. I found that all of the queries written in views/packages/procedures/functions are JOINING tables at WHERE clause level. Example

SELECT * FROM
  TABLE_A A,
  TABLE_B B,
  TABLE_C C,
  TABLE_D D
WHERE 
    A.ID=B.ID(+)
AND B.NO=C.NO(+)
AND C.STATUS=D.ID
AND C.STATUS NOT LIKE 'PENDING%'

This query applies only to ORACLE since it has the (+) join qualifier which is not acceptable in other SQL platforms. The above query is equivalent to:

SELECT * FROM
  TABLE_A A LEFT JOIN TABLE_B B ON A.ID=B.ID
  LEFT JOIN TABLE_C C ON B.NO=C.NO
  JOIN TABLE_D D ON C.STATUS=D.ID
WHERE
      C.STATUS NOT LIKE 'PENDING%'

Non of the queries I have seen is written with join taking place in the FROM clause.

My question can be divided into three parts:

Q: Assuming that I have the same Oracle environment, which query is better in terms of performance, cache, CPU load, etc. The first one (joining at WHERE) or the second (joining at FROM)

Q: Is there any other implementation of SQL that accepts the (+) join qualifier other than oracle? if yes, which?

Q: Maybe having the join written at WHERE clause makes the query more readable but compromises the ability to LEFT/RIGHT join, that's why the (+) was for. Where can I read more about the origin of this (+) and why it was invented specifically to Oracle?

Answer 1

Q1. No difference. You can check it using profiling and compare execution plan.

Q2. As I know, only oracle support it. But it is not recommended to use in latest version of Oracle RDBMS:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

Q3. Oracle "invent" (+) before outer join was specified in SQL ANSI.

Answer 2

There should be no performance difference. Assuming you're on a vaguely recent version of Oracle, Oracle will implicitly convert the SQL 99 syntax to the equivalent Oracle-specific syntax. Of course, there are bugs in all software so it is possible that one or the other will perform differently because of some bug. The more recent the version of Oracle, the less likely you'll see a difference.

The (+) operator (and a variety of other outer join operators in other databases) were created because the SQL standard didn't have a standard way of expressing an outer join until the SQL 99 standard. Prior to then, every vendor created their own extensions. It took Oracle a few years beyond that to support the new syntax. Between the fact that bugs were more common in the initial releases of SQL 99 support (not common but more common than they are now), the fact that products needed to continue to support older database versions that didn't support the new syntax, and people being generally content with the old syntax, there is still plenty of code being written today that uses the old Oracle syntax.