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 `null`s. – Pacerier Apr 12 '15 at 17:44 — Pacerier, Apr 12 '15 at 17:44

score 421 · Accepted Answer · edited Aug 12 '11 at 11:57

421

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 '11 at 11:57

Abel

56,041
24
146
247

answered Aug 01 '10 at 23:23

OMG Ponies

325,700
82
523
502

18

which is, in general, the fastest of the above? – Hampus Brynolf Apr 10 '12 at 18:02
@HampusBrynolf: This is MySQL specific - it depends on if the columns [are nullable](http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/) or [are not nullable](http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/) – OMG Ponies Apr 11 '12 at 01:57
2

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 :) – Anna May 15 '14 at 17:52
11

FYI, here's a useful discussion of the relative efficiency of these three methods: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ – moustachio Jun 08 '14 at 14:02
@OMGPonies, For the `exists` part, Why do you use `select null` instead of `select *`? Isn't it recommended to use `select *`? – Pacerier Apr 11 '15 at 14:58
@OMGPonies, Your answers [are wrong](http://stackoverflow.com/a/3384137/632951) because it makes the implicit assumption that the columns are `not null`. – Pacerier Apr 11 '15 at 15:18
What if the BLOB table is in an attached/separate DB? Thanks! – bunkerdive Apr 29 '16 at 03:13
As my observation, DELETE NOT IN is the slowest one, sometimes hundred times slower. NOT EXISTS and LEFT JOIN is much more faster. LEFT JOIN is a bit faster than NOT EXISTS. Of course it depends on your table structure. But generally NOT IN queries runs slower. – endo64 Jul 26 '16 at 15:39
2

@Pacerier - "wrong" is a bit strong. To make sure people understand, the answers *do* work if `fileid`is *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. – ToolmakerSteve Sep 26 '16 at 07:42
1

In addition, the only "wrong" consequence of the first two, is that they will not delete rows of BLOB where `fileid` is null. (Actually, the first one might - to be sure, I'd have to test it.) BTW, for robust design, it should be a non-nullable field. – ToolmakerSteve Sep 26 '16 at 07:59
5

For people attempting this w/SQLite: see [this answer](http://stackoverflow.com/a/4967229/786356) – bunkerdive Oct 14 '16 at 17:06
For some reason the MySQL on my host server would only execute the first method. Even then, the PHPMyAdmin 4.5.1 syntax checker wouldn't accept anything between `DELETE` and `FROM`, but the query ran ok anyway when I pressed Go. – clayRay Mar 24 '20 at 07:28
Why is `f` needed in the 3. solution? – Ömer An Oct 01 '21 at 04:17
What is the difference between NOT EXISTS and NOT IN? – Biax20 May 02 '22 at 14:39
Found that "DELETE FROM x..." throws error and "DELETE x FROM x..." works alright. Found it here: https://stackoverflow.com/questions/2763206/deleting-rows-with-mysql-left-join – Jacobski Aug 07 '23 at 01:33

score 33 · Answer 2 · answered Aug 01 '10 at 23:20

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 01 '10 at 23:20

Martin Smith

438,706
87
741
845

What is *"/*This line is unlikely to be needed but using NOT IN...*/"* supposed to mean? – Pacerier Apr 10 '15 at 14:13
1

@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 Apr 10 '15 at 16:40
Wow good catch. So the answer by omgponies is wrong! `not in(null)` is pretty logical, Why does it not work? What's the rationale behind that? – Pacerier Apr 11 '15 at 14:41
1

@Pacerier [because `unknown` and anything is either `false` or `unknown` and can never evaluate to `true`](http://stackoverflow.com/a/2973572/73226) – Martin Smith Apr 11 '15 at 21:04
What if the BLOB table is in an attached/separate DB? Thanks! – bunkerdive Apr 29 '16 at 03:14
1

@bunkerdive Then use three part object names that include the database name. – Martin Smith Apr 29 '16 at 09:32
@MartinSmith ...sry, I'm sucha noob. That means DB.Table.Col? – bunkerdive Apr 29 '16 at 14:31
2

@Pacerier - The important sentence is "This line is unlikely to be needed". `id` traditionally is a non-nullable primary key. – ToolmakerSteve Sep 26 '16 at 07:45

score 21 · Answer 3 · answered Jul 05 '13 at 06:18

21

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

answered Jul 05 '13 at 06:18

George

6,006
6
48
68

1

I think there is a `files.id` and `blob.fileid`. I'm guessing your query will result in an error. – jww Dec 20 '19 at 00:45

score 1 · Answer 4 · answered Mar 03 '22 at 16:16

1

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 03 '22 at 16:16

Royer Adames

868
9
13

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

4 Answers4

Using LEFT JOIN/IS NULL:

Using NOT EXISTS:

Using NOT IN:

Warning

Linked

Related