Left join and excluding non-existent rows on certain matchups

Question

fiddle.

TABLE1
PROP1   PROP2   NUM
a       a       1
a       a       2
a       a       3
a       b       1
a       b       2
a       b       3

TABLE2
PROP1   PROP2   NUM
a       a       1
a       a       2

I want to query the missing NUM values in TABLE2 with regards to (PROP1, PROP2) tuples such as (a,b,3). However, if a tuple does not exist in TABLE2 such as (a,b). I don't want to return it in the result.

So, my expected output is

PROP1   PROP2   NUM
a       a       3

Following query I wrote, returns the (a,b) tuples as well, which I don't want to.

SELECT *
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.PROP1 = T2.PROP1 AND T1.PROP2 = T2.PROP2 AND T1.NUM = T2.NUM
WHERE T2.NUM IS NULL

I want to exclude these 3 rows, so I join with TABLE2 one more time and group the results which gives me the correct result.

SELECT T1.PROP1, T1.PROP2, T1.NUM
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.PROP1 = T2.PROP1 AND T1.PROP2 = T2.PROP2 AND T1.NUM = T2.NUM
JOIN TABLE2 T22 ON T1.PROP1 = T22.PROP1 AND T1.PROP2 = T22.PROP2
WHERE T2.NUM IS NULL
GROUP BY T1.PROP1, T1.PROP2, T1.NUM

Question: Is there any way I can do it without a GROUP BY statement since it is a bit exhaustive for large tables.

I'm using Oracle 11g.

Answer 1

This will do what you want, but I don't know if it will be more efficient:

SELECT *
FROM TABLE1 T1 JOIN
     (SELECT DISTINCT PROP1, PROP2
      FROM TABLE2
     ) TT2
     ON T1.PROP1 = TT2.PROP1 AND t1.PROP2 = TT2.PROP2 LEFT JOIN
     TABLE2 T2
     ON T1.PROP1 = T2.PROP1 AND T1.PROP2 = T2.PROP2 AND T1.NUM = T2.NUM
WHERE T2.NUM IS NULL;

It first filters table1 on the matching rows, and then does the final comparison.

Answer 2

That's how I would have done it

with table1 as
(select 'a' prop1, 'a' prop2, 1 num from dual
union all
select 'a' , 'a' , 2  from dual
union all
select 'a' , 'a' , 3  from dual
union all
select 'a' , 'b' , 1  from dual
union all
select 'a' , 'b' , 2  from dual
union all
select 'a' , 'b' , 3 from dual),
table2 as(
select 'a' prop1, 'a' prop2, 1 num from dual
union all
select 'a' , 'a' , 2  from dual
)
select prop1, prop2, num
from table1
where (prop1, prop2) in (select prop1, prop2 from table2)
minus
select prop1, prop2, num
from table2

another approach is this:

select prop1, prop2, num
from table1
where (prop1, prop2, num) not in(select prop1, prop2, num
from table2)
and (prop1, prop2) in (select prop1, prop2 from table2)

Edit: I tried playing around with exists to get it, to use table2 just once, but I don't find a solution for that, I would be interessted if someone else finds a solution for that.

Answer 3

Based on Gordon's answer about the performance, the IS NULL might be a performance issue.

IS NULL suppresses the index usage. Because NULL values are not indexed.

There are two ways to make the use of index with IS NULL :

1.BITMAP index. However, more applicable in OLTP systems.

2.My favourite way, and nice to demonstrate. We could make the leaves of the b-tree index a constant. Thus, making use of index while querying for NULL. Basically, the NULLs are all together, at the top/bottom of the index. Oracle can use the index forwards or backwards, so doesn't really matter. And it does a full scan of the index.

I have answered a similar question here http://www.orafaq.com/forum/mv/msg/194746/625371/#msg_625371

The first scenario won't use the index due to the OR is null condition :

SQL> SELECT * FROM PROD_NEW;

PROFILE_TYPE
---------------

Prod
Prodparallel

Prod

SQL> CREATE INDEX PROD_NEW_I1 ON PROD_NEW
  2    (PROFILE_TYPE
  3    );

Index created.

SQL> EXPLAIN PLAN FOR SELECT * FROM PROD_NEW WHERE PROFILE_TYPE = 'Prod' OR PROFILE_TYPE IS NULL;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2121244107

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     3 |    15 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PROD_NEW |     3 |    15 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("PROFILE_TYPE" IS NULL OR "PROFILE_TYPE"='Prod')

13 rows selected

Let's make the leaves constant :

SQL> DROP INDEX PROD_NEW_I1;

Index dropped.

SQL> CREATE INDEX PROD_NEW_I1 ON PROD_NEW
  2    (PROFILE_TYPE,1
  3    );

Index created.

SQL> EXPLAIN PLAN FOR SELECT * FROM PROD_NEW WHERE PROFILE_TYPE = 'Prod' OR PROFILE_TYPE IS NULL;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1272076902

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     3 |    15 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | PROD_NEW_I1 |     3 |    15 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("PROFILE_TYPE" IS NULL OR "PROFILE_TYPE"='Prod')

13 rows selected.

SQL>

I answered to a similar question here, Oracle. Select all if parameter is null else return specific item issue