Search code examples
sqloracle-databasejoinwhere-clause

Is there any performance advantage using non-equi join instead of where clause in Oracle SQL?

I've been studying Oracle SQL non-equi join and while there are so many different use cases, I noticed that joining tables using a range of values with non-equi join is basically the same as using WHERE clause.

Code from a great non-equi join tutorial here

SELECT r.id, r.name, h.id, h.address, h.rent, h.bedrooms
FROM renters r
JOIN houses h
ON h.district = r.preferred_district
    AND h.rent BETWEEN r.min_rent AND r.max_rent
    AND h.bedrooms >= r.min_bedrooms
WHERE h.id NOT IN (SELECT house_id FROM deals);

In the above example, I think AND h.rent BETWEEN r.min_rent AND r.max_rent part and AND h.bedrooms >= r.min_bedrooms can be placed in WHERE clause and return exactly the same result.

So what's the point of using non-equi join here?

My guess is that by applying constraints during the join operation would be more performant since the joined table would be smaller.

Is my guess correct? Is there anything I'm missing?

Thanks in advance mates.

Solution

  • The easiest way to get insight in such topic is to use EXPLAIN PLAN. You get an overview of the join type and the used access and filter predicate.

    Here an example of the (bit simplified) statement using part of the conditions in the where clause

    EXPLAIN PLAN  SET STATEMENT_ID = 'jara2' into   plan_table  FOR
SELECT r.id, r.name, h.id, h.address, h.rent, h.bedrooms
FROM renters r
JOIN houses h
ON h.district = r.preferred_district
where h.rent BETWEEN r.min_rent AND r.max_rent;
    
 
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara2','ALL'));

    Result - cut for brevity

    ------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |   208 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |         |     1 |   208 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| RENTERS |     1 |   104 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| HOUSES  |     1 |   104 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("H"."DISTRICT"="R"."PREFERRED_DISTRICT")
       filter("H"."RENT">="R"."MIN_RENT" AND "H"."RENT"<="R"."MAX_RENT")

    The exact same excution plan you'll see for the original statement with the full join condition without where clause.

    You can your guess is not correct, in both cases Oracle makes a hash join with the equal predicate (access predicate for id 1) and then throw away the rows that does not match the filter predicate for id 1)

    So the swaping of predicates between the join consition and WHERE clause is not relavent for non-equi joins (except for estetics, readability and correct SQL), but BTW is very relevant for OUTER JOINS as you can get a different results.