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?

Sergey N Lukin · Accepted Answer · 2013-11-27T06:25:28.217

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.

score 4 · Answer 2 · answered Nov 25 '13 at 07:54

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.

score 0 · Answer 3 · answered Nov 25 '13 at 07:51

A1:

As far as I know they vary in-terms of syntax not in performance. So there is no difference between joining at 'where' clause and joining at 'from' clause.

A2:

To answer this in better way, 'Joining at FROM' clause is standard across all the platforms. So forget about (+) symbols

A3 I have worked in Oracle for sometimes. People use (+) symbols for left/right join because it's easy to write. Some ppl use join at (FROM) clause because it's more readable and understandable.

Hope these points helps you. Please let me know incase am wrong with anything.

score 0 · Answer 4 · answered Nov 27 '13 at 06:50

One difference between Oracle syntax and ANSI syntax is: In Oracle syntax you cannot make a FULL OUTER JOIN, there you have to use ANSI syntax.

Oracle introduced ANSI syntax in Oracle 9i - including several bugs. In the meantime since Oracle 11 or 12 it works quite well, but you may discover some obstacles in Oracle 9/10.

Another advandage of ANSI Join syntax is you cannot forget any join condition.

"SELECT * FROM TABLE_A, TABLE_B" performs implicitly a Cross-Join. "SELECT * FROM TABLE_A JOIN TABLE_B" raise an error, you are forced to provide the join condition. If you want to Cross-Join you have to specify it, i.e. "SELECT * FROM TABLE_A CROSS JOIN TABLE_B"

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

4 Answers4