SQLite - Count how many records there are for each foreign key

Question

• I need a query that counts the primary keys that were not assigned in the foreign key. In short: with the count value of 0 when not appearing in the foreign key. See image:
• My query returns only the count of the primary keys that have become foreign key in another table. My query does not show the values ​​0.

I'm using:

SELECT l.name AS listName, COUNT(p.fk_list_identifier) AS countNum
FROM list AS l INNER JOIN person AS p 
WHERE l.identifier = p.fk_list_identifier
GROUP BY l.name;

Answer 1

If you just want the lists with no reference, you can do:

SELECT l.name AS listName
FROM list l LEFT JOIN
     person AS p 
     on l.identifier = p.fk_list_identifier
WHERE p.fk_list_identifier IS NULL
GROUP BY l.name;