Search code examples
sqlsqlitecountsubqueryrelational-division

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

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
Solution

  • 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.