1

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.

4
  • 2
    what's the point of using non-equi join here? Common logic. In general you place the joining conditions which sets the relations (whose expressions includes the columns from both tables) to ON clause whereas the filtering conditions (whose expressions uses the columns from one table only) are placed to WHERE.
    – Akina
    Dec 27, 2022 at 8:12
  • 2
    My guess is that by applying constraints during the join operation would be more performant since the joined table would be smaller. Incorrect. These forms are completely equivalent. And they must produce the same execution plan.
    – Akina
    Dec 27, 2022 at 8:13
  • 1
    The optimiser will apply joins and filtering conditions as it sees fit. It makes no difference what order you write them in. Dec 29, 2022 at 13:09
  • Thanks. I didn't know optimizer would be so smart as I don't really know any internals of it. Jan 9, 2023 at 6:42

1 Answer 1

1

Logically, join conditions (equi-join or otherwise) belong in the JOIN clause, and filter conditions belong in the WHERE clause (although with outer joins you have to either place any filters in the JOIN clause or else handle nulls in the WHERE clause, which can complicate things). However, the optimiser will decide the join order and and apply filtering conditions as it sees fit regardless of what order you write them in.

create table houses 
( id                 integer generated as identity
, address            varchar2(1000)
, district           varchar2(100)
, rent               number(6,2)
, bedrooms           integer );

create table renters
( id                 integer generated as identity
, name               varchar2(100)
, preferred_district varchar2(100)
, min_bedrooms       integer
, max_bedrooms       integer
, min_rent           number(6,2)
, max_rent           number(6,2) );
insert into houses (address, district, rent, bedrooms)
select dbms_random.string('a',30) as address
     , decode(round(dbms_random.value(0,7)),
              1, 'Camden',
              2, 'Islington',
              3, 'Haringey',
              4, 'Hackney',
              5, 'Westminster',
              6, 'Tower Hamlets', 'Lambeth') as district
     , round(dbms_random.value(1000,3000),-2) as rent
     , round(dbms_random.value(1,4)) as bedrooms
from   xmltable('1 to 1000');

insert into renters (name, preferred_district, min_bedrooms, max_bedrooms, min_rent, max_rent)
select name, preferred_district
     , min_bedrooms, round(min_bedrooms + dbms_random.value(0,2)) as max_bedrooms
     , min_rent, min_rent +200 as max_rent
from   (
    select dbms_random.string('a',30) as name
         , decode(round(dbms_random.value(0,5)),
                  1, 'Camden',
                  2, 'Islington',
                  3, 'Haringey',
                  4, 'Hackney', 'Lambeth') as preferred_district
         , round(dbms_random.value(1,2)) as min_bedrooms
         , round(dbms_random.value(1000,2000),-2) as min_rent
    from   xmltable('1 to 10000')
);

commit;

The following two queries are logically the same, and the optimiser generates the same execution plan:

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;

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
and    h.bedrooms >= r.min_bedrooms;

Plan for both in my test (19.0):

 Plan Hash Value  : 786460833 

---------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows | Bytes   | Cost | Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  188 |  140812 |   42 | 00:00:01 |
| * 1 |   HASH JOIN          |         |  188 |  140812 |   42 | 00:00:01 |
|   2 |    TABLE ACCESS FULL | HOUSES  | 1000 |  593000 |    5 | 00:00:01 |
|   3 |    TABLE ACCESS FULL | RENTERS | 9028 | 1408368 |   25 | 00:00:01 |
---------------------------------------------------------------------------

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

Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
1
  • Sorry for the late acceptance as I was on vacation. At first I was confused because as you said "plan for both", I thought there should be two separate test results. Now I understand that because the two queries follow the same execution plan (by optimizer), the test result is the same for the both. Jan 9, 2023 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.