How to select items which belong to one group but not another

Question

Sqlite3

How do I select animals which are only pets and not food? POF is "pet or food" column. An animal can belong to both groups. This is a smaller version of the real problem. I don't want to split this into more tables.

animal  pof
----------
fish    pet
fish    food
pig     food
cat     pet
dog     pet
horse   pet
mouse   pet
duck    pet
duck    food
cow     food
rabbit  pet
rabbit  food
gerbil  pet
worm    <null>
chicken food

I have the following, but it seems awkward:

SELECT * from 
(SELECT  NAME, POF, count(*) as cnt
 FROM    ANIMALS
 GROUP BY NAME) AS GC
 WHERE GC.cnt == 1 AND GC.POF == 'pet'

Yielding correctly:

NAME    POF cnt
---------------
cat     pet  1
dog     pet  1
gerbil  pet  1
horse   pet  1
mouse   pet  1

Answer 1

One method uses aggregation:

select animal, count(*) cnt
from animals
group by animal
having 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 animal
from animals a
where 
    pof = 'pet' 
    and not exists (select 1 from animals a1 where a1.animal = a.animal and a1.pof <> 'pet')

Answer 2

Use NOT IN to exclude all the animals that have pof = 'food':

select *
from animals
where 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'
except
select animal from animals where pof = 'food'

See the demo.

Answer 3

Another way you can use aggregation

select animal
from animals
group by animal
having avg(case when pof='pet' then 1 else 0 end)=1;