SQL - Selecting all rows with non matching null rows

Question

How can I select all rows which have a non matching null row? Given the following table, any row with the foreign key 1 should not be returned since a corresponding row with a NULL exists. How could I only select rows with the foreign keys 2 and 3?

foreign_key | created_at
1             12345...
1             12345...
2             12345...
3             12345...
1             NULL

Answer 1

You can use not exists:

select *
from mytable t
where not exists (
    select 1 
    from mytable t1 
    where t1.foreign_key = t.foreign_key and t1.created_at is null
)

Another option is to use window functions; here is one approach using boolean windowing:

select *
from (
    select t.*, bool_or(created_at is null) over(partition by foreignkey) has_null
    from mytable t
) t
where not has_null