Select rows where all in a group are not null postgresql

Question

I have two tables: groups(group_id), member(group_id, name) and I would like to select all the members in groups where everyone in the group has a non-null name. For example, if this is the members table:

group_id|name
1|a
1|b
2|c
2|null
3|null
3|null

then the result of the query should return:

group_id|name
1|a
1|b

I tried running

SELECT * FROM members M1 
WHERE ALL(SELECT M2.name IS NOT NULL FROM members M2)
ORDER BY M1.group_id

but it didn't work.

Answer 1

Use bool_and():

select group_id, name
from members
where group_id in (
    select group_id
    from members
    group by 1
    having bool_and(name is not null)
    );

Answer 2

SELECT 
    *
FROM groups g
INNER JOIN members m
ON g.group_id = m.group_id
WHERE NOT EXISTS (SELECT * FROM members mbr WHERE mbr.name IS NULL AND mbr.group_id = m.group_id)

Essentially, we select all records, except for those where we can find a null name record with the same group ID.

Note that I don't believe this is SARG-able, so if you have a massive database that relies on indexes, this may be a bit on the slow side.