How to delete rows using an outer join

Question

I've got a problem to delete records from a PostgreSQL table, using a LEFT JOIN.

I'd like to delete rows I get with the following query:

SELECT * FROM url 
LEFT JOIN link_type ON url.link_type = link_type.id 
WHERE link_type.id IS NULL

To do so, here is what I did:

 DELETE FROM url
 USING link_type
 WHERE url.link_type = link_type.id  AND link_type.id IS NULL

Query works but doesn't delete anything, although that's exactly what's explained in the doc: http://www.postgresql.org/docs/current/static/sql-delete.html.

Is my problem due to IS NULL in the query or Am I missing something?

It doesn't delete anything because when link_type.id is null, url.link_type = link_type.id is not true so these two conditions are never satisfied together. — Daniel Vérité
– Daniel Vérité, Commented Apr 16, 2013 at 10:42

marcj · Accepted Answer · 2013-04-16 15:28:44Z

13

Good work, sun. Minor suggestion: when using EXISTS/NOT EXISTS you don't need to SELECT *. A common convention (docs) is to just write SELECT 1 as like this:

DELETE FROM url WHERE NOT EXISTS (
  SELECT 1 FROM link_type WHERE url.link_type = link_type.id 
);

Functionally, both ways work.

answered Apr 16, 2013 at 15:28

marcj

3461 silver badge5 bronze badges

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

1 Comment

Cherona Over a year ago

I tried this query on two tables with 300k, and 600k rows respectively, and the query timed out after 30 minutes.

SuN · Accepted Answer · 2013-04-16 10:34:30Z

7

Still don't understand why my previous query doesn't work (if someone could explain, would be nice), but here is how I did the trick:

DELETE FROM url WHERE NOT EXISTS (SELECT * FROM link_type WHERE url.link_type = link_type.id );

answered Apr 16, 2013 at 10:34

SuN

1,0562 gold badges12 silver badges25 bronze badges

1 Comment

Guedes Over a year ago

Because if "url.link_type = link_type.id" is "TRUE" then "link_type.id" is not "NULL", if "link_type.id" is "NULL" the first condition "url.link_type = link_type.id" will returns "NULL" too then you get "NULL AND TRUE" that is just "NULL". Since NULL is a unknow value you cannot say that it is TRUE or FALSE, so no rows is displayed. The NOT EXISTS will to the work for you.

Collectives™ on Stack Overflow

How to delete rows using an outer join

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related