Wow it's really worse then you make it look. Running a "normal" join there are 55 rows returned. There is a significant difference in the plans. The natural join is joining on both DEPTMENT_ID and MANAGER_ID, hence anyone with w null manager ID is being tossed out. Maybe that is what you want I don't know but casually looking at it I would have thought of just a join on DEPARTMENT_ID. This is the Normal join plan: SQL> get e_d_1.sql 1 SELECT EMP.EMPLOYEE_ID, EMP.DEPARTMENT_ID, DEPT.DEPARTMENT_NAME FROM EMPLOYEES EMP, DEPARTMENTS DEPT 2 WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID and DEPT.DEPARTMENT_ID in (10,20,30,40,50) 3* ORDER BY EMP.DEPARTMENT_ID, EMP.EMPLOYEE_ID 4 SQL> SQL> @hxplan Enter .sql file name (without extension): e_d_1 Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] : Plan hash value: 2480766633 ------------------------------------------------------------------------ ----------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------ ----------------------------- | 0 | SELECT STATEMENT | | 22 | 506 | 5 (20)| 00:00:01 | | 1 | SORT ORDER BY | | 22 | 506 | 5 (20)| 00:00:01 | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | | 22 | 506 | 4 (0)| 00:00:01 | | 4 | INLIST ITERATOR | | | | | | | 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 5 | 80 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 5 | | 1 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 4 | | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 4 | 28 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------ ----------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("DEPT"."DEPARTMENT_ID"=10 OR "DEPT"."DEPARTMENT_ID"=20 OR "DEPT"."DEPARTMENT_ID"=30 OR "DEPT"."DEPARTMENT_ID"=40 OR "DEPT"."DEPARTMENT_ID"=50) 7 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID") filter("EMP"."DEPARTMENT_ID"=10 OR "EMP"."DEPARTMENT_ID"=20 OR "EMP"."DEPARTMENT_ID"=30 OR "EMP"."DEPARTMENT_ID"=40 OR "EMP"."DEPARTMENT_ID"=50) SQL> This is the natural join plan: SQL> get e_d_2.sql 1 SELECT EMPLOYEE_ID, DEPARTMENT_ID, DEPARTMENT_NAME FROM EMPLOYEES 2 NATURAL JOIN DEPARTMENTS where DEPARTMENT_ID in (10,20,30,40,50) 3* ORDER BY DEPARTMENT_ID, EMPLOYEE_ID 4 SQL> SQL> @hxplan Enter .sql file name (without extension): e_d_2 Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] : Plan hash value: 2480766633 ------------------------------------------------------------------------ ----------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------ ----------------------------- | 0 | SELECT STATEMENT | | 2 | 60 | 4 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 2 | 60 | 4 (25)| 00:00:01 | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | | 2 | 60 | 3 (0)| 00:00:01 | | 4 | INLIST ITERATOR | | | | | | |* 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 2 | 38 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 5 | | 1 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 4 | | 0 (0)| 00:00:01 | |* 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 11 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------ ----------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("DEPARTMENTS"."MANAGER_ID" IS NOT NULL) 6 - access("DEPARTMENTS"."DEPARTMENT_ID"=10 OR "DEPARTMENTS"."DEPARTMENT_ID"=20 OR "DEPARTMENTS"."DEPARTMENT_ID"=30 OR "DEPARTMENTS"."DEPARTMENT_ID"=40 OR "DEPARTMENTS"."DEPARTMENT_ID"=50) 7 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID") filter("EMPLOYEES"."DEPARTMENT_ID"=10 OR "EMPLOYEES"."DEPARTMENT_ID"=20 OR "EMPLOYEES"."DEPARTMENT_ID"=30 OR "EMPLOYEES"."DEPARTMENT_ID"=40 OR "EMPLOYEES"."DEPARTMENT_ID"=50) 8 - filter("EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID") -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hemant K Chitale Sent: Sunday, February 24, 2013 10:16 AM To: ORACLE-L Subject: Natural Join Bug in 11.2.0.1 ?? 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 -- //www.freelists.org/webpage/oracle-l