I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group.

I want to delete all rows from link_group where no related row in link_reply exists. Sounds basic enough but I've been struggling with it.

Will it be something simple like this (not working)?

DELETE FROM link_group WHERE link_reply = NULL;
  • do you have a DDL for everyone to look at?
    – dizzystar
    Commented Apr 3, 2016 at 17:18
  • Take a look at the MINUS operator. You have to specify a field in links_reply.
    – Vérace
    Commented Apr 3, 2016 at 17:40
  • DELETE FROM links_group USING links_group AS lg LEFT JOIN links_reply AS lr ON lg.col= lr.some_other_col WHERE links_reply.some_other_col IS NULL
    – Mihai
    Commented Apr 3, 2016 at 18:52
  • 1
    I had a similar question, which also takes concurrency in account. See dba.stackexchange.com/questions/251875.
    – pbillen
    Commented Oct 24, 2019 at 15:20

1 Answer 1


Quoting the manual:

There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the USING clause. Which technique is more appropriate depends on the specific circumstances.

Bold emphasis mine. Using information that is not contained in another table is a tad bit tricky, but there are easy solutions. From the arsenal of standard techniques to ...

... a NOT EXISTS anti-semi-join is probably simplest and most efficient for DELETE:

DELETE FROM link_group lg
   SELECT FROM link_reply lr
   WHERE  lr.which_group = lg.link_group_id

Assuming (since table definitions are not provided) link_group_id as column name for the primary key of link_group.

The technique @Mihai commented works as well (applied correctly):

DELETE FROM link_group lg
USING  link_group      lg1
LEFT   JOIN link_reply lr ON lr.which_group = lg1.link_group_id
WHERE  lg1.link_group_id = lg.link_group_id
AND    lr.which_group IS NULL;

But since the table expression in the USING clause is joined to the target table (lg in the example) with a CROSS JOIN, you need another instance of the same table as stepping stone (lg1 in the example) for the LEFT JOIN, which is less elegant and typically slower.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.