How to select items which belong to one group but not another
Use NOT IN to exclude all the animals that have pof = 'food':
select *from animalswhere pof = 'pet'and animal not in (select animal from animals where pof = 'food')Or if you want only the column animal you can use EXCEPT:
select animal from animals where pof = 'pet'exceptselect animal from animals where pof = 'food'See the demo.
One method uses aggregation:
select animal, count(*) cntfrom animalsgroup by animalhaving min(pof) = max(pof) an min(pof) = 'pet'If there are no duplicates, as shown in your data, the count is always 1... and you can use not exists to produce the same result (depending on your data, this might, or might not, be more efficient):
select animalfrom animals awhere pof = 'pet' and not exists (select 1 from animals a1 where a1.animal = a.animal and a1.pof <> 'pet')
Another way you can use aggregation
select animalfrom animalsgroup by animalhaving avg(case when pof='pet' then 1 else 0 end)=1;