Oracle vs ANSI SQL Syntax

Sections:

  1. Intro
  2. A way to avoid ORA-01417
  3. Advantages of ANSI syntax
  4. Example SQL – comparing Oracle vs. ANSI syntax

Intro

You might notice that I mainly use ANSI SQL syntax on this site, instead of Oracle syntax.

I used to write SQL in Oracle using Oracle syntax – such as:

    SELECT fu.user_name
      FROM applsys.fnd_user fu
         , ar.hz_parties hp
         , hr.per_all_people_f papf
     WHERE fu.person_party_id = hp.party_id(+)
       AND papf.party_id = hp.party_id(+)
       AND hp.party_type = 'PERSON';

However, if I wrote the SQL above and tried to run it, I’d get this error when doing so because I am trying to outer join the hz_parties table to the per_all_people_f and fnd_user at the same time:

ORA-01417: a table may be outer joined to at most one other table

A way to avoid ORA-01417

A colleague said he uses ANSI syntax, where you explicitly write the join names in your SQL.

    SELECT hp.party_name
         , hp.party_number
         , papf.employee_number
         , fu.user_name
      FROM ar.hz_parties hp
 LEFT JOIN hr.per_all_people_f papf ON hp.party_id =        papf.party_id
 LEFT JOIN applsys.fnd_user fu      ON fu.person_party_id = hp.party_id
     WHERE hp.party_type = 'PERSON'
       AND hp.creation_date > '01-OCT-2015';

Advantages of ANSI syntax

There are various advantages to using this approach:

  • You can outer join a table to more than one other table – in the example above, the hz_parties table is outer joined to the per_all_people_f and fnd_user, and there is no error.
  • You can introduce a table name and join all in the same line, so that it looks a lot tidier – if you want to comment out a table, just comment out the line with the table name and join on it, instead of in two places when using Oracle syntax. Also all of the info is in one place – e.g. the table name and join details.
  • The “WHERE” part of your SQL can be used to restrict your query to include specific records, instead of needing to include many lines of joins, so the SQL is more readable.
  • If you get using to writing SQL using ANSI syntax, you can use that in other databases such as MySQL, Access and SQL Server.

This is a useful post on Stackoverflow which goes into more detail: Oracle Joins – Comparison between conventional syntax VS ANSI Syntax

The SQLs below compare two statements, one written with Oracle syntax, the other with ANSI. There is no difference in the cost when viewing the Explain Plan for the 2 statements.

It’s probably a matter of preference, and doesn’t make a lot of difference really, as the statements below look very similar, but I think I prefer the ANSI format as I think it looks tidier and I find it easier to understand.

Example SQL – comparing Oracle vs. ANSI syntax

-- requisition headers and lines and line_locations_all, joined through to PO Headers and Lines
-- outer joined to po tables so can get REQ details which are not linked to PO

  SELECT prha.segment1 requisition, prha.requisition_header_id
       , prha.creation_date
       , prha.authorization_status
       , prla.line_num
       , prla.unit_price
       , prla.quantity
       , pha.segment1 po
       , pha.po_header_id
       , pla.line_num po_line_number
       , pla.po_line_id
       , plla.line_location_id
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
       , po.po_line_locations_all plla
       , po.po_lines_all pla
       , po.po_headers_all pha
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prla.line_location_id = plla.line_location_id(+)
     AND plla.po_line_id = pla.po_line_id(+)
     AND plla.po_header_id = pha.po_header_id(+)
     AND prha.creation_date > '23-NOV-2015'
ORDER BY prha.creation_date DESC
       , prha.segment1
       , prla.line_num;
       
-- requisition headers and lines and line_locations_all, joined through to PO Headers and Lines
-- left joined to po tables so can get REQ details which are not linked to PO

      SELECT prha.segment1 requisition, prha.requisition_header_id
           , prha.creation_date
           , prha.authorization_status
           , prla.line_num
           , prla.unit_price
           , prla.quantity
           , pha.segment1 po
           , pha.po_header_id
           , pla.line_num po_line_number
           , pla.po_line_id
           , plla.line_location_id
        FROM po.po_requisition_headers_all prha
        JOIN po.po_requisition_lines_all prla ON prha.requisition_header_id = prla.requisition_header_id
   LEFT JOIN po.po_line_locations_all plla    ON prla.line_location_id =      plla.line_location_id
   LEFT JOIN po.po_lines_all pla              ON plla.po_line_id =            pla.po_line_id
   LEFT JOIN po.po_headers_all pha            ON plla.po_header_id =          pha.po_header_id
       WHERE prha.creation_date > '23-NOV-2015'
    ORDER BY prha.creation_date DESC
           , prha.segment1
           , prla.line_num;

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*