[SQL]How to delete all rows containing NULL value on the joined table column using LEFT JOIN and DELETE

Question

I am trying to get rid of every row that has no relation to the other table.

This is what I tried:

DELETE obj FROM objednavky obj 
    LEFT JOIN polozky_objednavek poObj
            ON poObj.objednavka_id = obj.id
            WHERE poObj.objednavka_id = null

`WHERE poObj.objednavka_id IS null` don't use `=` to compare aginst NULL. — forpas, Aug 07 '19 at 12:22

score 0 · Answer 1 · answered Aug 07 '19 at 12:18

0

DELETE is only working on one table, so it should not be joined. you can use the other table in your WHERE clause though, like this:

DELETE FROM objednavky obj 
  WHERE( SELECT objednavka_id FROM polozky_objednavek WHERE objednavka_id=objednavky.id) IS NULL

If you're doing this manually I recommend running the SELECT clause first (or at least a COUNT variant) to check you're not going to accidentally wipe out the entire table.

answered Aug 07 '19 at 12:18

Rob Lambden

2,175
6
15

You absolutely can join other tables in a `DELETE` statement. OP's syntax looks good, apart from using `= NULL` instead of `IS NULL` – cf_en Aug 07 '19 at 12:34
1

@cf_en - you may have misunderstood my comment, which I admit was not clear. I didn't say you can't join for a delete, only that you shouldn't. Who says you shouldn't is a valid question. Including the other tables in the join can make it appear to someone that the intent is to delete from multiple tables .. so not joining makes the code clearer (in my opinion - I recognise other opinions are available!) – Rob Lambden Aug 07 '19 at 13:51
That's a fair comment. I've read opinion pieces that say you shouldn't join tables in `DELETE` and `UPDATE` statements. Personally I think it's OK, it's useful sometimes for filtering and targeting the records to delete or update. Apparently MySql also allows you to delete from multiple tables in a single statement, which seems confusing and potentially dangerous. – cf_en Aug 08 '19 at 08:13
@cf_en - so perhaps another reason why you *shouldn't* do it? – Rob Lambden Aug 08 '19 at 08:20
@RobLambden DELETE statement is absolutely working for 2 and more tables check here: https://stackoverflow.com/a/1233479/11776868 – Blue Print Aug 08 '19 at 08:37
@Blue Print - as per my comments above I haven't said this doesn't work ... only that (in my opinion) it's clearer not to write statements that intend to only delete from one table with tables that are JOINed. I believe that the SQL standard stipulates that DELETE should not delete from tables that are JOINed (although I believe you can use DELETE t1.*, t2.* FROM ....) although different implementations may support 'cascade deletes' or an expression that deletes from multiple tables as cf_en has suggested. My comment was about making the intent of the statement clear. – Rob Lambden Aug 08 '19 at 08:46
@RobLambden Oh, then it was a misunderstanding. I think you missed what you sayed in your first sentence, you state: "DELETE is only working on one table, so it should not be joined." - that is equivalent to : "DELETE is not working on more than one table, so it should not be joined." I am not reffering to the fact if it is better to use JOIN or SELECT. – Blue Print Aug 08 '19 at 08:57
Personally I prefer to `JOIN` tables into a `DELETE` statement than to use subqueries in the `WHERE` clause to apply filtering/targeting for the delete. The intent should be clear to anyone with a basic knowledge of SQL. Targeting two tables for a `DELETE`, though, goes too far :) – cf_en Aug 08 '19 at 09:16

Zeshan · Answer 2 · 2019-08-07T13:09:36.873

0

You can use NOT EXISTS. Try this:

DELETE FROM objednavky obj
WHERE NOT EXISTS (
      SELECT
          *
      FROM
          polozky_objednavek poObj
      WHERE
           `poObj`.`objednavka_id` = `obj`.`id`);

Demo

edited Aug 07 '19 at 13:09

answered Aug 07 '19 at 12:22

Zeshan

2,496
3
21
26

It says this: #1054 - Unknown column 'obj.id' in 'where clause' – Blue Print Aug 07 '19 at 12:50
Is `id` column exists on `objednavky` table? – Zeshan Aug 07 '19 at 12:53
@BluePrint Can you try run the query again? I updated my answer. – Zeshan Aug 07 '19 at 13:10
Glad to hear. I just added the `backticks` around column names. You can accept the answer if you think it was helpful :) – Zeshan Aug 07 '19 at 17:25
I would, but it is not really answering my question; "using JOIN and DELETE" – Blue Print Aug 07 '19 at 20:46
I think It does what you need to do. – Zeshan Aug 07 '19 at 21:21

score 0 · Accepted Answer · answered Aug 07 '19 at 12:57

DELETE obj FROM objednavky obj 
    LEFT JOIN polozky_objednavek poObj
            ON poObj.objednavka_id = obj.id
            WHERE poObj.objednavka_id IS null

thanks to @forpas for comenting, I just needed to change:

item = NULL

to

item IS NULL

and worked itexactly as I needed. Keep in mind that SQL doesn't support this type of comparison.

[SQL]How to delete all rows containing NULL value on the joined table column using LEFT JOIN and DELETE

3 Answers3