SELECT * FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id AND d.DEPARTMENT_ID > 100

很显然and条件先于join执行

SELECT * FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.DEPARTMENT_ID > 100

SELECT * FROM employees e LEFT JOIN  departments d ON e.department_id =

d.department_id  WHERE d.DEPARTMENT_ID > 100



Plan hash value: 2599337293



----------------------------------------------------------------------------------------------------------------------

| Id  | Operation		       | Name	       	   | Starts | E-Rows | A-Rows |	  A-Time   | Buffers | Reads |

----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT	       |                   |	  1 |	     |	    2 |00:00:00.01 |	  8 |	   1 |

|   1 |  NESTED LOOPS		       |       	           |	  1 |	     |	    2 |00:00:00.01 |	  8 |	   1 |

|   2 |   NESTED LOOPS		       |                   |	  1 |	   1 |	    2 |00:00:00.01 |	  6 |	   1 |

|   3 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |	  1 |	   2 |	    2 |00:00:00.01 |	  4 |	   1 |

|*  4 |     INDEX RANGE SCAN	       | EMP_DEPARTMENT_IX |	  1 |	   2 |	    2 |00:00:00.01 |	  2 |	   1 |

|*  5 |    INDEX UNIQUE SCAN	       | DEPT_ID_PK	   |	  2 |	   1 |	    2 |00:00:00.01 |	  2 |	   0 |

|   6 |   TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS	   |	  2 |	   1 |	    2 |00:00:00.01 |	  2 |	   0 |

----------------------------------------------------------------------------------------------------------------------





Predicate Information (identified by operation id):

---------------------------------------------------





   4 - access("E"."DEPARTMENT_ID">100)

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

       filter("D"."DEPARTMENT_ID">100)



26 rows selected.

SELECT * FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id AND d.DEPARTMENT_ID > 10 WHERE d.MANAGER_ID > 200

SELECT * FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id AND d.DEPARTMENT_ID > 10 WHERE d.MANAGER_ID > 200 Plan hash value: 3971452728 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name |Starts | E-Rows | A-Rows | A-Time | Buffers| ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 6 | 00:00:00.01 | 9 | | 1 | NESTED LOOPS | | 1 | | 6 | 00:00:00.01 | 9 | | 2 | NESTED LOOPS | | 1 | 9 | 6 | 00:00:00.01 | 7 | |* 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 1 | 4 | 00:00:00.01 | 4 | |* 4 | INDEX RANGE SCAN | DEPT_ID_PK | 1 | 26 | 26 | 00:00:00.01 | 2 | |* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 4 | 10 | 6 | 00:00:00.01 | 3 | | 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 9 | 6 | 00:00:00.01 | 2 | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("D"."MANAGER_ID">200) 4 - access("D"."DEPARTMENT_ID">10) 5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."DEPARTMENT_ID">10)

from (join/AND) on where group by having order by rownum

ITPub博客

SQL 执行顺序你懂的

最新文章

ITPub博客

SQL 执行顺序 你懂的

最新文章

SQL 执行顺序你懂的