Re: Natural Join Bug in 11.2.0.1 ??

  • From: Hemant K Chitale <hemantkchitale@xxxxxxxxx>
  • To: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
  • Date: Sun, 24 Feb 2013 23:57:42 +0800

Thanks.  I like to rewrite it using a JOIN USING  on DEPARTMENT_ID
select employee_id, department_id, department_name

from employees join departments

using (department_id)

where department_id in (10,20,30,40,50)

order by department_id, employee_id

/




On Sun, Feb 24, 2013 at 11:53 PM, Sayan Malakshinov <xt.and.r@xxxxxxxxx>wrote:

> Hi,
>
> You forgot about MANAGER_ID column. Your query with natural join equal to:
>
> select employee_id, e.department_id, department_name
> from employees e
>    , departments d
> where
>      d.department_id = e.department_id
>  and d.manager_id    = e.manager_id
>  and d.department_id in (10,20,30,40,50)
> order by e.department_id, e.employee_id
> /
>  or
> select employee_id, e.department_id, department_name
> from employees e
>      join departments d
>        on  d.department_id = e.department_id
>        and d.manager_id    = e.manager_id
> where
>   d.department_id in (10,20,30,40,50)
> order by e.department_id, e.employee_id
> /
>
>
> On Sun, Feb 24, 2013 at 7:15 PM, Hemant K Chitale <
> hemantkchitale@xxxxxxxxx> wrote:
>
>> While creating some sample SQLs for my OCA 11g students, I found what
>> seems
>> to be a bug in
>> 11.2.0.1  Oracle OTN Developer Days VM
>> select employee_id, department_id, department_name
>> from employees natural join departments
>> where department_id in (10,20,30,40,50)
>> order by department_id, employee_id
>> /
>>
>> EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
>> ----------- ------------- ------------------------------
>>         202            20 Marketing
>>         115            30 Purchasing
>>         116            30 Purchasing
>>         117            30 Purchasing
>>         118            30 Purchasing
>>         119            30 Purchasing
>>         129            50 Shipping
>>         130            50 Shipping
>>         131            50 Shipping
>>         132            50 Shipping
>>         184            50 Shipping
>>         185            50 Shipping
>>         186            50 Shipping
>>         187            50 Shipping
>>
>> 14 rows selected.
>>
>> Strangely, these two are not reported with my data :  (This looks like a
>> Bug)
>> EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
>> ----------- ------------- ------------------------------
>>         200            10 Administration
>>         203            40 Human Resources
>>
>> SQL> l
>>   1  select e.employee_id, e.department_id, d.department_name
>>   2  from employees e, departments d
>>   3  where e.department_id = d.department_id
>>   4* and d.department_id in (10,40)
>> SQL> /
>>
>> EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
>> ----------- ------------- ------------------------------
>>         200            10 Administration
>>         203            40 Human Resources
>>
>> SQL>
>> SQL> l
>>   1  select e.employee_id, e.department_id, d.department_name
>>   2  from employees e, departments d
>>   3  where e.department_id = d.department_id
>>   4* and e.department_id in (10,40)
>> SQL> /
>>
>> EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
>> ----------- ------------- ------------------------------
>>         200            10 Administration
>>         203            40 Human Resources
>>
>> SQL>
>>
>>
>>
>> --
>>
>> Hemant K Chitale
>> http://hemantoracledba.blogspot.com
>> http://hemantscribbles.blogspot.com
>> http://web.singnet.com.sg/~hkchital
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Senior performance tuning engineer
> PSBank
> http://orasql.org
>



-- 

Hemant K Chitale
http://hemantoracledba.blogspot.com
http://hemantscribbles.blogspot.com
http://web.singnet.com.sg/~hkchital


--
//www.freelists.org/webpage/oracle-l


Other related posts: