In Oracle, is this a cross join with a filter or a inner join?-CodePudding

I came across this in some legacy sql code and was wondering how it worked 'under the hood'. In the example below, does the sql engine treat this as a cross join with a filter or a regular inner join on the 'LIKE' keyword?

Select 
        t1.col1,
        t2.col2
From
        table1 t1,
        table2 t2
Where t1.approved           IS NULL
        AND UPPER(NVL(t1.team, '%'))            LIKE UPPER(NVL(t2.TEAM, '%'))
        ;

CodePudding user response：

It is effectively an INNER JOIN:

SELECT t1.col1,
       t2.col2
FROM   table1 t1
       INNER JOIN table2 t2
       ON (   NVL(UPPER(t1.team), '%') LIKE UPPER(t2.team)
           OR t2.team IS NULL)
WHERE  t1.approved IS NULL;

CodePudding user response：

If you want to see how it works "under the hood", run an explain plan.

The Oracle optimizer will rewrite your query into a different form when it actually parses it. For example, I just ran a test, and all 3 of these queries:

select *
from table1 t1, table2 t2
where nvl(t1.team,'%') like nvl(t2.team, '%');

select *
from table1 t1
inner join table2 t2
on nvl(t1.team,'%') like nvl(t2.team, '%');

select *
from table1 t1
cross join table2 t2
where nvl(t1.team,'%') like nvl(t2.team, '%');

Were translated into this same explain plan in my environment:

SELECT STATEMENT  FIRST_ROWS Cost: 13  Bytes: 437,265  Cardinality: 1,845       
3 NESTED LOOPS  Cost: 13  Bytes: 437,265  Cardinality: 1,845    
    1 TABLE ACCESS FULL TABLE MYSCHEMA.TABLE1 Cost: 2  Bytes: 36,162  Cardinality: 369  
    2 TABLE ACCESS FULL TABLE MYSCHEMA.TABLE2 Cost: 11  Bytes: 256,455  Cardinality: 1,845

Meaning that as far as Oracle was concerned, given the specific tables and number of rows and statistics that it has available, these 3 queries are identical. And they are, for most purposes, logically equivalent.

However, this is a simple query. If you start adding more WHERE conditions, or if the data in the tables were structured differently, or if you have indexes, the optimizer might choose different plans, and the queries might perform differently.

CodePudding user response：

Cross join is full cartesian product. It results all possible combination between two tables. And cross join with a filter can be inner join if filter references two table's column. Your SQL is surely inner join because it is not cartesian product and references two table's column.

You can use inner join in various form like

where substr(t1.col1, 1, 2) = t2.col2
where t1.col1 between t2.col1 and t2.col2