WHERE CLAUSE IN JOIN vs WHERE CLAUSE AFTER JOIN
RAVEE, December 14, 2016 - 4:38 pm UTC
SELECT SUM(COALESCE(T2.R,0))
FROM (SELECT ROWNUM AS R FROM DUAL CONNECT BY LEVEL < 20) T1
LEFT JOIN (SELECT ROWNUM AS R FROM DUAL CONNECT BY LEVEL < 20) T2 ON T2.R=T1.R
WHERE T2.R=20
;
SELECT SUM(COALESCE(T2.R,0))
FROM (SELECT ROWNUM AS R FROM DUAL CONNECT BY LEVEL < 20) T1
LEFT JOIN (SELECT ROWNUM AS R FROM DUAL CONNECT BY LEVEL < 20) T2 ON T2.R=T1.R AND T2.R=20
;
December 14, 2016 - 5:00 pm UTC
Those queries are not the same!
If you have a where on the table you're outer joining to, you effectively make the query an inner join. When the predicate is in the ON clause, you filter the rows from T2, then outer join those results to T1.
Same example with sample schemas OE
Daniel, March 15, 2020 - 7:52 pm UTC
Very good answer before! The same condition in the "ON" clause is not the same as in the "WHERE" clause. Just use the ON for the JOIN only and the WHERE for the filtering. Consider these two queries:
1. The JOIN is done correctly, between DEPARTMENTS and EMPLOYEES, then we select the deparments with no EMPLOYEES assigned to it (17 rows):
select d.department_id, d.department_name, e.employee_id
from departments d
left outer join employees e
on d.DEPARTMENT_ID = e.DEPARTMENT_ID
where e.department_id is null;
2. If we want to achive the same but we put the filter clause in the "ON" part, we'll have a totally different result (27 rows), because we first filter the rows in the employees table which are NULL (1 row), the cross join it the ones in DEPARTMENTS !!!:
select d.department_id, d.department_name, e.EMPLOYEE_ID
from departments d
left outer join employees e
on d.DEPARTMENT_ID = e.DEPARTMENT_ID and e.department_id is null;
THE EXPLAIN PLAN FOR BOTH QUERIES (SEE THE "ROWS" VALUES)
The explain plan for the first query:
1.
Plan hash value: 222687530
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 391 | 5 (20)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | MERGE JOIN OUTER | | 17 | 391 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 107 | 749 | 3 (34)| 00:00:01 |
| 6 | VIEW | index$_join$_002 | 107 | 749 | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN | | | | | |
| 8 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 107 | 749 | 1 (0)| 00:00:01 |
| 9 | INDEX FAST FULL SCAN | EMP_EMP_ID_PK | 107 | 749 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."DEPARTMENT_ID" IS NULL)
5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))
filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))
7 - access(ROWID=ROWID)
The explain plan for the second query:
2.
Plan hash value: 545319043
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 621 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 27 | 621 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1 | 7 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 7 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))
filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))
6 - access("E"."DEPARTMENT_ID"(+) IS NULL)
March 16, 2020 - 10:24 am UTC
nice stuff.
ON vs. WHERE inner join filter difference
Norbert, May 16, 2022 - 12:13 pm UTC
Normally, I would only put the JOIN condition into the ON clause, and the filter condition into the WHERE clause, however the advantages of putting both in the the ON condition is an easy rewrite to a left outer join and also an easy sub-query factoring test run.
So, I would like to know from the original question's example, if the "h.je_category = 'Receipts'" filter placed inside the ON clause vs. putting it into the WHERE clause could cause any performance difference in Oracle? Thanks a lot.
May 17, 2022 - 3:38 pm UTC
They should work the same.
Personally, I think it's better to put filtering criteria in the WHERE clause - this makes the distinction between filter and join criteria easier to spot.
With the outer join case above, I consider this to be part of the join.