PostgreSQL Get all rows where its id is not anywhere in another column for a table

Question

--------------+-------------------+
           id |         parent_id |
--------------+-------------------|
           18 |            <null> |
           20 |            <null> |
           25 |                18 |
--------------+-------------------+

I want my query to select all ids where its id doesn't exist in parent_id column. How would I go about doing that?

jarlh · Accepted Answer · 2016-04-01 09:11:01Z

1

Easiest way is to do use NOT EXISTS to return those id's not found in the parent_id column:

select id
from tablename t1
where not exists (select 1 from tablename t2
                  where t2.parent_id = t1.id)

NOT IN may also be used, but be careful, NULL's have to be taken care of:

select id
from tablename
where id not in (select parent_id from tablename
                 where parent_id is not null)

answered Apr 1, 2016 at 9:11

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Get all rows where its id is not anywhere in another column for a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related