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

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


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.


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.


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

A3I 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.