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