Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Left Join Bug?

Aketi JyuuzouJun 21 2006 — edited Jun 23 2006
SQL> select * from v$version;

BANNER
---------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1
PL/SQL Release 9.0.1.1.1 - Production
CORE    9.0.1.1.1       Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - P
NLSRTL Version 9.0.1.1.1 - Production

SQL> set null NULL
SQL> select a.aaa,b.bbb from
  2  (select 1 as aaa from dual) a
  3  left join (select 1 as bbb from dual
  4       union select 2 from dual
  5       union select 3 from dual) b
  6  on a.aaa=b.bbb and b.bbb=2;

AAA  BBB
---  ----
  1  NULL

SQL> select a.aaa,b.bbb from
  2  (select 1 as aaa from dual) a
  3  left join (select 1 as bbb from dual
  4       union select 2 from dual
  5       union select 3 from dual) b
  6  on a.aaa=b.bbb and 2=b.bbb;

AAA  BBB
---  ---
  1    1

Why different?

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 21 2006
Added on Jun 21 2006
4 comments
1,318 views