Delete sql rows where IDs do not have a match from another table

Question

I'm trying to delete orphan entries in a mysql table.

I have 2 tables like this:

Table files:

id	....
1	....
2	....
7	....
9	....

table blob:

fileid	....
1	....
2	....
3	....
4	....
4	....
4	....
9	....

The fileid and id columns can be used to join the tables together.

I want to delete all rows in table blob where fileid cannot be found in the table files.id.

So using the example above that would delete rows: 3 & 4(s) in the blob table.

Skip to the second answer if you are using nulls.

Pacerier
– Pacerier

2015-04-12 17:44:54 +00:00
Commented Apr 12, 2015 at 17:44 — Pacerier
– Pacerier, Commented Apr 12, 2015 at 17:44

Abel · Accepted Answer · 2011-08-12 11:57:13Z

427

Using LEFT JOIN/IS NULL:

DELETE b FROM BLOB b 
  LEFT JOIN FILES f ON f.id = b.fileid 
      WHERE f.id IS NULL

Using NOT EXISTS:

DELETE FROM BLOB 
 WHERE NOT EXISTS(SELECT NULL
                    FROM FILES f
                   WHERE f.id = fileid)

Using NOT IN:

DELETE FROM BLOB
 WHERE fileid NOT IN (SELECT f.id 
                        FROM FILES f)

Warning

Whenever possible, perform DELETEs within a transaction (assuming supported - IE: Not on MyISAM) so you can use rollback to revert changes in case of problems.

edited Aug 12, 2011 at 11:57

Abel

57.5k25 gold badges159 silver badges260 bronze badges

answered Aug 1, 2010 at 23:23

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

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

15 Comments

Hampus Brynolf Over a year ago

which is, in general, the fastest of the above?

Anna Over a year ago

For some reason, deleting using the LEFT JOIN didn't work on MS SQL Server Mgmt Studio (not sure why; it just complained about the LEFT JOIN). Anyone knows why is that? It worked using NOT EXISTS though :)

moustachio Over a year ago

FYI, here's a useful discussion of the relative efficiency of these three methods: explainextended.com/2009/09/18/…

ToolmakerSteve Over a year ago

@Pacerier - "wrong" is a bit strong. To make sure people understand, the answers do work if fileidis non-nullable. Also, the third solution (NOT IN) only requires that f.id by non-nullable. Presumably that is a primary key, so it would be.

bunkerdive Over a year ago

For people attempting this w/SQLite: see this answer

|

Martin Smith · Accepted Answer · 2010-08-01 23:20:45Z

33

DELETE FROM blob 
WHERE fileid NOT IN 
       (SELECT id 
        FROM files 
        WHERE id is NOT NULL/*This line is unlikely to be needed 
                               but using NOT IN...*/
      )

answered Aug 1, 2010 at 23:20

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

8 Comments

Pacerier Over a year ago

What is "/*This line is unlikely to be needed but using NOT IN...*/" supposed to mean?

Martin Smith Over a year ago

@Pacerier - NOT IN (NULL) returns an empty result set so NULLs need to be excluded. But an id column probably won't be nullable anyway hence "unlikely to be needed"

Martin Smith Over a year ago

@Pacerier because unknown and anything is either false or unknown and can never evaluate to true

Martin Smith Over a year ago

@bunkerdive Then use three part object names that include the database name.

ToolmakerSteve Over a year ago

@Pacerier - The important sentence is "This line is unlikely to be needed". id traditionally is a non-nullable primary key.

|

George · Accepted Answer · 2013-07-05 06:18:26Z

24

DELETE FROM blob
WHERE NOT EXISTS (
    SELECT *
    FROM files
    WHERE id=blob.id
)

answered Jul 5, 2013 at 6:18

George

6,1426 gold badges51 silver badges69 bronze badges

1 Comment

jww Over a year ago

I think there is a files.id and blob.fileid. I'm guessing your query will result in an error.

Royer Adames · Accepted Answer · 2022-03-03 16:16:06Z

2

DELETE FROM <table> 
WHERE <row column you want to delete by > not in 
(select <column you want to compare other column> from <other table>)

This deletes the row when the first column doesn't appear on the second table

answered Mar 3, 2022 at 16:16

Royer Adames

1,08414 silver badges13 bronze badges

Collectives™ on Stack Overflow

Delete sql rows where IDs do not have a match from another table

4 Answers 4

Using LEFT JOIN/IS NULL:

Using NOT EXISTS:

Using NOT IN:

Warning

15 Comments

8 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Using LEFT JOIN/IS NULL:

Using NOT EXISTS:

Using NOT IN:

Warning

15 Comments

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related