Performance difference of constraint placement in SQL join-on

Question

Is there any difference in prepared query structure and thence performance, between

select * 
from employee e
join division d
    on e.eid = d.eid
    and e.div = d.div
    and e.level > 5
    and e.startDate > sysdate - interval '60' month
;

and:

select * 
from employee e
join division d
    on e.eid = d.eid
    and e.div = d.div
where 
    e.level > 5
    and e.startDate > sysdate - interval '60' month
;

This is a faq. (And can clearly be expected to be.) Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, May 05 '20 at 22:53
@philipxy: Thanks, didn't know that this was a faq, and wow, how many times it has been asked. A good answer is here: https://stackoverflow.com/questions/16202285/sql-where-versus-on-inner-join — wolφi, May 05 '20 at 23:16
@wolφi & BlessedGeek Reasonable minimal research re SQL performance immediately leads to relational & SQL query engine optimization/implementation basics & indexes, plans, statistics & SARGability. [Tips for asking a good SQL question](https://meta.stackoverflow.com/a/271056/3404097) Ask re optimization after you have learned & applied those basics. — philipxy, May 05 '20 at 23:37

score 5 · Answer 1 · answered May 05 '20 at 21:26

Both queries are syntactically equivalent. They will produce the same result.

I would not expect any performance difference between them. Regardless of whether you put the conditions in the where clause or in the on clause of the joins, the optimizer should produce an identical execution plan.

score 2 · Answer 2 · answered May 05 '20 at 21:35

Only in some low-end database engines there could be some differences.

However, you tagged this question for the Oracle database. In Oracle, after the query is parsed, the "rewriting phase" comes in, and in Oracle that phrase is pretty well implemented. I'm sure Oracle will rewrite both queries internally in the same way. Therefore, they should be fully equivalent.

score 2 · Answer 3 · answered May 05 '20 at 23:03

The best way to answer such questions is compare the query plans of both versions.

For instance, in SQL Developer, you just click "Explain Plan" or hit F10, pin the results for both query versions and compare the two plans.

Alternatively, you can issue DBMS_XPLAN.DISPLAY_CURSOR immediately after the query:

SELECT * FROM employee e
  JOIN division d
    ON e.div = d.div
   AND e.lev > 5
   AND e.startDate > DATE '2020-01-01';

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);   

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |   174 (100)|          |
|   1 |  MERGE JOIN                  |          |   149 |  9834 |   174   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DIVISION |    25 |   300 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PKDIV    |    25 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |   149 |  8046 |   172   (2)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEE |   149 |  8046 |   171   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DIV"="D"."DIV")
       filter("E"."DIV"="D"."DIV")
   5 - filter(("E"."STARTDATE">TO_DATE(' 2020-01-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND "E"."LEV">5))

And for the second version:

SELECT * 
  FROM employee e
  JOIN division d
    ON e.div = d.div
 WHERE e.lev > 5
   AND e.startDate > DATE '2020-01-01';

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);   

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |   174 (100)|          |
|   1 |  MERGE JOIN                  |          |   149 |  9834 |   174   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DIVISION |    25 |   300 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PKDIV    |    25 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |   149 |  8046 |   172   (2)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEE |   149 |  8046 |   171   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DIV"="D"."DIV")
       filter("E"."DIV"="D"."DIV")
   5 - filter(("E"."STARTDATE">TO_DATE(' 2020-01-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND "E"."LEV">5))

Both plans look identical to me.

Performance difference of constraint placement in SQL join-on

3 Answers3