Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Oracle 9.2.05 bug with INNER JOIN ANSI?

Posted on 2005-04-12
Medium Priority
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 48

Assisted Solution

schwertner earned 400 total points
ID: 13761925
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 JOIN T2 ON T1.C1 = T2.C3
      SELECT * FROM T1 WHERE T1.C1 = T2.C3

LVL 25

Assisted Solution

jrb1 earned 400 total points
ID: 13761931
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 a.id = b.id

can also be written as

select ....
from a
   , b
where a.id = b.id

Two ways to write left outer join:

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

select ....
from a left outer join b
on a.id = b.id

Accepted Solution

Pierrick LOUBIER earned 1200 total points
ID: 13761992
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...

Author Comment

ID: 13762000
Ok.. got it for innner joins but what about POST oracle 9i notation...how 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.

Author Comment

ID: 13762158
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

577 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