Databases
StackOverflow2013
Postsdbo
POOracle Joins - Comparison between conventional syntax VS ANSI Syntax
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle Joins - Comparison between conventional syntax VS ANSI Syntax
    primarykey
    data
    text
    <p><strong>Preamble</strong></p> <p>Of late, I see too many geeks commenting on Oracle questions saying that "Do not use (+) operator, rather use JOIN syntax".</p> <p><strong>Question</strong></p> <p>I do see that both work well. But what is the real difference between using them and what makes you feel using them? I would welcome answers, more from experience.</p> <pre><code> 1. Is there anything to do with limitations in application, performance, etc. while using them? 2. What would you suggest for me? </code></pre> <p>I did read something on <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2080416" rel="nofollow noreferrer">Oracle documentation</a> but not good enough to make me understand or feel comfortable with the comprehensive information.</p> <p><strong>Note</strong>: I am planning to migrate 200+ packages and procedures, if the Keyword should be used instead of (+)</p> <pre><code> 3. Also is there any freeware tools to do the rewrite? </code></pre> <p><strong>Posting samples</strong></p> <pre><code>┌───────────────────────────────────┬─────────────────────────────────────────────┐ │ INNER JOIN - CONVENTIONAL │ INNER JOIN - ANSI SYNTAX │ ├───────────────────────────────────┼─────────────────────────────────────────────┤ │ SELECT │ SELECT │ │ emp.deptno │ ename, │ │ FROM │ dname, │ │ emp, │ emp.deptno, │ │ dept │ dept.deptno │ │ WHERE │ FROM │ │ emp.deptno = dept.deptno; │ scott.emp INNER JOIN scott.dept │ │ │ ON emp.deptno = dept.deptno; │ ├───────────────────────────────────┼─────────────────────────────────────────────┤ │ LEFT OUTER JOIN - CONVENTIONAL │ LEFT OUTER JOIN - ANSI SYNTAX │ ├───────────────────────────────────┼─────────────────────────────────────────────┤ │ SELECT │ SELECT │ │ emp.deptno │ ename, │ │ FROM │ dname, │ │ emp, │ emp.deptno, │ │ dept │ dept.deptno │ │ WHERE │ FROM │ │ emp.deptno = dept.deptno(+); │ scott.emp LEFT OUTER JOIN scott.dept │ │ │ ON emp.deptno = dept.deptno; │ ├───────────────────────────────────┼─────────────────────────────────────────────┤ │ RIGHT OUTER JOIN - CONVENTIONAL │ RIGHT OUTER JOIN - ANSI SYNTAX │ ├───────────────────────────────────┼─────────────────────────────────────────────┤ │ SELECT │ SELECT │ │ emp.deptno │ ename, │ │ FROM │ dname, │ │ emp, │ emp.deptno, │ │ dept │ dept.deptno │ │ WHERE │ FROM │ │ emp.deptno(+) = dept.deptno; │ scott.emp RIGHT OUTER JOIN scott.dept │ │ │ ON emp.deptno = dept.deptno; │ ├───────────────────────────────────┼─────────────────────────────────────────────┤ │ FULL OUTER JOIN - CONVENTIONAL │ FULL OUTER JOIN - ANSI SYNTAX │ ├───────────────────────────────────┼─────────────────────────────────────────────┤ │ SELECT │ SELECT │ │ * │ * │ │ FROM │ FROM │ │ emp, │ scott.emp FULL OUTER JOIN scott.dept │ │ dept │ ON emp.deptno = dept.deptno; │ │ WHERE │ │ │ emp.deptno = dept.deptno(+) │ │ │ UNION ALL │ │ │ SELECT │ │ │ * │ │ │ FROM │ │ │ emp, │ │ │ dept │ │ │ WHERE │ │ │ emp.deptno(+) = dept.deptno │ │ │ AND emp.deptno IS NULL; │ │ └───────────────────────────────────┴─────────────────────────────────────────────┘ </code></pre> <p><strong>PS</strong>: Read the summary of answers for all updates grouped.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSriniV
    1. USSriniV
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTDuplicate
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle Joins - Comparison between conventional syntax VS ANSI Syntax
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POOracle Joins - Comparison between conventional syntax VS ANSI Syntax
      1. USSriniV
      1. VTFavorite
    3. VO
      singulars
      1. POOracle Joins - Comparison between conventional syntax VS ANSI Syntax
      1. USJon Heller
      1. VTFavorite
    1. COOne very good reason to use explicit `JOIN`s rather than implicit (regardless wether they are outer joins or not) is that it's *much* easier to accidently create a cartesian product with the implicit joins. With explicit `JOIN`s you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.
      singulars
      1. POOracle Joins - Comparison between conventional syntax VS ANSI Syntax
      1. USa_horse_with_no_name
    2. CORelated question: [Difference between Oracle's plus (+) notation and ansi JOIN notation?](http://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation) Basically `(+)` is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS.
      singulars
      1. POOracle Joins - Comparison between conventional syntax VS ANSI Syntax
      1. USVincent Malgrat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload