Oracle 9.2.05 bug with INNER JOIN ANSI?

Posted on 2005-04-12
Last Modified: 2008-01-09

I'm working on a medium size project and have recently deployed the code to our client.  When work began we agreed on Oracle as the version and all my SQL statements work fine with this version.  Now I drop the code to an Oracle flavor of the database and by queries start having lots of problems.

The DBA there told me that INNER JOIN and LEFT OUTER JOIN ANSI queries will fail in

My question is:  Is this true?  If so, not being a big PL/SQL expert, what can I replace the INNER JOIN AND LEFT OUTER JOIN keywords with?  I need to fix this fast!


Question by:rdytmire
    LVL 47

    Assisted Solution

    LEFT OUTER JOIN - A join between two tables which returns the result of the INNER join as well as
    unmatched rows from the LEFT table is a LEFT OUTER join.
        This query returns rows from the departments table even if no employees are
        assigned to it.
        SELECT employee_id, department_name
        FROM departments d
        LEFT OUTER JOIN employees e
        ON (e.department_id= d.department_id);
    This is equivalent to the following pre-Oracle9i outer join notation:
        SELECT employee_id, department_name
        FROM departments d, employees e
        WHERE e.department_id(+) = d.department_id;

    Inner Join:
    The "ordinary" join that joins rows from the two tables based on the relationship of the values in columns of the tables. If "inner" is not specified then it is implicit. Merely specifying "join" is equivalent to specifying "inner join".
          SELECT * FROM T1 INNER JOIN T2 ON T1.C1 = T2.C3
          SELECT * FROM T1 JOIN T2 ON T1.C1 = T2.C3
          SELECT * FROM T1 WHERE T1.C1 = T2.C3

    LVL 25

    Assisted Solution

    I don't see any documentation saying that it's a bug in  That's a big patchset for earlier version of 9.

    Anyway, 2 ways to do an inner join:

    select ....
    from a inner join b
    on =

    can also be written as

    select ....
    from a
       , b
    where =

    Two ways to write left outer join:

    select ....
    from a
       , b
    where = (+)

    select ....
    from a left outer join b
    on =
    LVL 8

    Accepted Solution

    ANSI Joins bugs fixed in (from Metalink) :

    2504168 Wrong results from query with ANSI outer join syntax and constant predicate  
    2618400 ORA-907 using ANSI JOIN with FROM clause subqueries  
    2711993 ANSI join with a set query block may produce wrong results  
    2846640 Ambiguous columns not signalled with ANSI join syntax (wrong results instead of error)  
    3087290 OERI[qcscpqbc1] possible from ANSI join  
    3089870 OERI[qcstxswrite1] with full outer join  
    3264527 OERI[12310] on SELECT with LEFT OUTER JOIN  
    3414440 Query rewrite may spin in qsmg_process_sqltxt for ANSI join in a view  
    3545642 Wrong result when using LEFT OUTER joins and TABLE() operator  
    3632885 In FULL OUTER JOIN a NESTED LOOPS ANTI is used when unique index exists  
    3696879 Wrong results from ANSI query with subquery in the ON condition  
    3786302 ANSI join with correlated TABLE() clause fails (ORA-904)  

    Your client should really apply the latest patchset...
    LVL 1

    Author Comment

    Ok.. got it for innner joins but what about POST oracle 9i to I replace LEFT OUTER JOIN notation?  I tried the (+) symbol in a test query here and no luck, appears to work just like a INNER JOIN.
    LVL 1

    Author Comment

    Thanks folks while the (+) syntax never did work correctly I finally was moved to accept ploubier's answer.  I will ask the data center to upgrade to rather than try to modify 50K lines of source code. :-)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now