Re: Natural Join Bug in 11.2.0.1 ??

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Sun, 24 Feb 2013 23:57:42 +0800
Message-ID: <CAMNBsZssy-0noczVkkjve=vE6FQnLt7qD=KHrEaZo6J8mBvhmA_at_mail.gmail.com>

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_at_gmail.com>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_at_gmail.com> 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
>>
>>
>> --
>> http://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


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

Received on Sun Feb 24 2013 - 16:57:42 CET