Partial Group Filter

Oracle 11g How can I get ALL pet_owners of FURRRY and SCALY and only earthworms and slugs ?

with genus_def 
as (
select 'FURRY' as genus, 'Stays warm in winter'        as genus_desc from dual union all
select 'SLIMY' as genus, 'Great for practical jokes'   as genus_desc from dual union all
select 'SCALY' as genus, 'Best viewed with scuba mask' as genus_desc from dual 
) ,
favorite_pets as
(
select 'FERGUS' pet_owner, 'MEERKET' as pet_1, 'FURRY' as genus from dual union all
select 'LAMAR' pet_owner, 'POLECAT' as pet_1, 'FURRY' as genus from dual union all
select 'LAMAR' pet_owner, 'SEABASS' as pet_1, 'SCALY' as genus from dual union all
select 'SUZIE' pet_owner, 'CLOWNFISH' as pet_1, 'SCALY' as genus from dual union all
select 'LAMAR' pet_owner, 'EARTHWORM' as pet_1, 'SLIMY' as genus from dual union all
select 'HEIDI' pet_owner, 'SLUG'      as pet_1, 'SLIMY' as genus from dual union all
select 'BRONX' pet_owner, 'SALAMANDER' as pet_1, 'SLIMY' as genus from dual)
select f.pet_owner, 
       f.pet_1,
       f.genus
from favorite_pets f inner join genus_def g on f.genus = g.genus
where g.genus in ('FURRY','SCALY');  --GET ALL FURRY, SCALY and only EARTHWORMS and SLUGS

**DESIRED RESULTS**
FERGUS   MEERKET    FURRY
LAMAR    POLECAT    FURRY
LAMAR    SEABASS    SCALY
SUZIE    CLOWNFISH  SCALY
LAMAR    EARTHWORM  SLIMY
HEIDI    SLUG       SLIMY

Answer

You want either of the two conditions to be met, so you need OR:

WHERE g.genus IN ('FURRY', 'SCALY') 
   OR f.pet_1 IN ('EARTHWORM', 'SLUG')