Si a alors b vérifie la clause where

L'un des problèmes est que - lorsqu'on regarde T2 - il n'est pas facile de dire si un "mappage" est correct ou non. Lors de la création des données de test pour T1 et T2, nous avons utilisé des CHAR pour VALUE1_ID, afin de rendre les requêtes suivantes un peu plus "lisibles".

Tables

-- T2 rows containing incorrect (invalid) mappings
--   -> all rows MINUS the correct ones
select T2.id, T2.value1_id, T2.value2_id
from T2
minus (
  select T2.id, T2.value1_id, T2.value2_id
  from T2
    join (
  --
    select T4.id categoryid, T3.id productid, M.category, M.product
    from T4
      join map M on T4.value2 = M.category
      join T3    on T3.value1 = M.product
  --
  ) C -- correct mappings
    on 
      C.productid = T2.value1_id
  and C.categoryid = T2.value2_id
) ;

-- result
ID  VALUE1_ID  VALUE2_ID  
2   07         NULL 

Requête refactorisée

-- in addition to tables T1, T2, T3, and T4: table with correct mappings
create table map( category, product )
as
select 'Fruit', 'Apples' from dual union all
select 'Cheese', 'Cheese' from dual union all
select 'Nuts', 'Cashews' from dual union all
select 'Fruit', 'Bananas' from dual union all
select 'Fruit', 'Cherries' from dual union all
select 'Candy', 'Skittles' from dual union all
select 'Candy', 'Chocolate' from dual union all
select 'Fruit', 'Pears' from dual union all
select 'Fruit', 'Kiwis' from dual union all
select 'Nuts', 'Almonds' from dual;

-- make sure that the entries in the MAP table tie in with T3 and T4
alter table map 
add (
  constraint m_pk primary key ( category, product )
, constraint m_category_fk foreign key ( category ) references T4 ( value2 )
, constraint m_product_fk foreign key ( product ) references T3 ( value1 )
) ;

DBfiddle

ALTERNATIVE▲

Une autre possibilité peut être: créer une table, contenant tous les mappages valides, dans formulaire "lisible par l'homme", et utilisez-le pour valider les mappages stockés dans T2. Cependant, utilisez l'approche qui vous convient le mieux, à condition d'obtenir les bons résultats. Exemple (testé avec Oracle 12c, 18c)

--
-- find incorrect mappings 
--
select t2.*, 'T1 id not valid' as status
from t2
where t2.id not in ( select id from T1 )
union all
select t2.*, 'value1_id <-> value2_id mapping incorrect '
from t1 join t2 on t1.id = t2.id
where
    ( t2.value1_id in ('01','04','05','08','09') and t2.value2_id <> 2 )
    or
    ( t2.value1_id = '02' and t2.value2_id <> 3 )
    or
    ( t2.value1_id in ('03','10') and t2.value2_id <> 1 )
    or
    ( t2.value1_id = '06' and t2.value2_id <> 4 )
    or
    ( t2.value1_id = '07' and t2.value2_id is null )
;

-- result
ID  VALUE1_ID  VALUE2_ID  STATUS                                      
4   10         1          T1 id not valid                             
4   09         2          T1 id not valid                             
2   07         NULL       value1_id <-> value2_id mapping incorrect 

Recherche de mappages incorrects

create table T1( id primary key ) 
as
select 1 from dual union all
select 2 from dual union all
select 3 from dual ;


create table T2 ( id, value1_id, value2_id )
as
select 1, '01', 2 from dual union all
select 1, '02', 3 from dual union all
select 1, '03', 1 from dual union all
select 2, '04', 2 from dual union all
select 2, '05', 2 from dual union all
select 2, '02', 3 from dual union all
select 2, '06', 4 from dual union all
select 2, '07', null  from dual union all
select 3, '08', 2 from dual union all
select 3, '02', 3 from dual union all
select 4, '09', 2 from dual union all
select 4, '10', 1 from dual ;

DBfiddle

Je vous recommande vivement de créer un tableau pour représenter la relation un-à-plusieurs entre T4 et T3 . Cela représenterait une première étape vers la correction de votre conception, tout en fournissant un moyen simple de résoudre votre question actuelle.

Voici une commande CREATE TABLE ... AS SELECT qui initialise une telle table avec votre exemple de données:

T1_ID | VALUE1_ID | VALUE2_ID
----: | --------: | --------:
    5 |         2 |         2
    6 |         4 |         3
    6 |        10 |         2
    7 |         7 |         2

Avec ce tableau en place, l'identification des enregistrements mal mappés est aussi simple que:

SELECT t2.*
FROM t2
WHERE t2.Value2_ID IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM cat WHERE cat.t3_id = t2.Value1_ID AND cat.t4_id = t2.Value2_ID
)

Ceci Démo DB Fiddle avec vos exemples de données donne: p>

create table cat AS
SELECT 1 t3_id, 2 t4_id FROM DUAL
UNION ALL SELECT 4, 2 FROM DUAL
UNION ALL SELECT 5, 2 FROM DUAL
UNION ALL SELECT 8, 2 FROM DUAL
UNION ALL SELECT 9, 2 FROM DUAL
UNION ALL SELECT 2, 3 FROM DUAL
UNION ALL SELECT 3, 1 FROM DUAL
UNION ALL SELECT 10, 1 FROM DUAL
UNION ALL SELECT 6, 4 FROM DUAL
UNION ALL SELECT 7, NULL FROM DUAL
;

Astuce pour améliorer encore votre conception: vous avez une relation un-à-plusieurs entre T4 (familles d'aliments) et T3 (aliments). La manière classique de représenter cela est d'ajouter une colonne dans la table enfant ( T3 ) qui fait référence à la table parent.

Configuration d'Oracle :

ID | VALUE1_ID | VALUE2_ID
-: | :-------- | --------:
 5 | 02        |         2
 6 | 04        |         3
 6 | 10        |         2
 7 | 07        |         2

Requête :

WITH mappings ( name, category ) AS (
  SELECT '01', 2 FROM DUAL UNION ALL
  SELECT '02', 3 FROM DUAL UNION ALL
  SELECT '03', 1 FROM DUAL UNION ALL
  SELECT '04', 2 FROM DUAL UNION ALL
  SELECT '05', 2 FROM DUAL UNION ALL
  SELECT '06', 4 FROM DUAL UNION ALL
  SELECT '07', NULL FROM DUAL UNION ALL
  SELECT '08', 2 FROM DUAL UNION ALL
  SELECT '09', 2 FROM DUAL UNION ALL
  SELECT '10', 1 FROM DUAL
)
SELECT *
FROM   T2 t
WHERE  NOT EXISTS (
  SELECT 1
  FROM   mappings m
  WHERE  t.value1_id = m.name
  AND    (  t.value2_id = m.category
         OR ( t.value2_id IS NULL AND m.category IS NULL ) )
);

Résultats

create table T2 ( id, value1_id, value2_id ) as
select 1, '01', 2 from dual union all
select 1, '02', 3 from dual union all
select 1, '03', 1 from dual union all
select 2, '04', 2 from dual union all
select 2, '05', 2 from dual union all
select 2, '02', 3 from dual union all
select 2, '06', 4 from dual union all
select 2, '07', null  from dual union all
select 3, '08', 2 from dual union all
select 3, '02', 3 from dual union all
select 4, '09', 2 from dual union all
select 4, '10', 1 from dual union all
select 5, '02', 2 from dual union all
select 5, '10', 1 from dual union all
select 6, '04', 3 from dual union all
select 6, '10', 2 from dual union all
select 7, '07', 2 from dual;

db fiddle ici