Sql DELETE with JOIN another table for WHERE condition

sql

join

delete

subqueries

byNikita Barsukov·Aug 22, 2024

To DELETE entries across multiple tables, either use a subquery or JOIN in the deletion query. Here's a clear subquery method:

DELETE FROM t1
WHERE id IN (
    SELECT t1.id FROM t1 JOIN t2 ON t1.fk = t2.id WHERE t2.cond = 'value'
);

Alternatively, a JOIN for databases like MySQL allows direct deletion:

DELETE t1 FROM t1 JOIN t2 ON t1.fk = t2.id WHERE t2.cond = 'value';

Replace t1, t2, fk, cond, and 'value' fittingly.

Make it clear: Using aliases

In complex scenarios, appropriately aliasing tables clarifies your kingdom. See below how to crown your tables with aliases:

DELETE a FROM table1 AS a
JOIN table2 AS b ON a.fk = b.id
WHERE b.condition = 'value';

It's like naming your own trustworthy knight of the table, right?

Be cautious: Maintaining referential integrity

Before you strive to perform a DELETE, beware if you are breaking the sacred law of referential integrity. Cascading deletes or friendly checks in your application can avoid leaving orphans in the kingdom.

Simple yet effective: Using NOT IN

Feel like JOINs are wearing too much armor for the battle? Your DBMS doesn't support it in DELETEs? Well, NOT IN is your light footed squire:

DELETE FROM t1
WHERE id NOT IN (
    SELECT fk FROM t2 WHERE t2.cond = 'value'
);

Null in shining armor: Handling NULL conditions

After a noble LEFT JOIN, rows that failed to find a match on the joined table shall be stamped NULL. To delete these unfortunates:

DELETE t1 FROM t1
LEFT JOIN t2 ON t1.fk = t2.id
WHERE t2.id IS NULL;

Ensure you test this on your battle simulation data (aka sample data) before striking the real battlefield of production data.

Be swift: Optimize with subqueries vs. JOINs

The sly fox says subqueries might run slower than JOIN operations. For larger data sets or when time is of the essence, favor the DELETE operation wielding a JOIN.

Aim true: Correct target table

Ensure that your DELETE query's crosshair is on the correct target table, especially dealing with aliases. Some databases might shout at you if the same table is in both the DELETE clause and the FROM clause. You wouldn't attack your ally, would you?

Errors on the horizon: Common pitfalls

Encounter a foe that says "target table cannot be updated"? This might be due to restrictions of your DBMS or incorrect SQL syntax. Your best ally is the DBMS documentation. Always check for quirk or specialty before drawing your sword.

LEFT JOIN – The White Knight

Spotting the traitors: Identifying deletable rows

Raise your shields and command LEFT JOIN to pick out rows in your primary table not mirrored in the other.

DELETE t1 FROM t1
LEFT JOIN t2 ON t1.id = t2.t1_id
WHERE t2.t1_id IS NULL;

In the above command, rows from t1 without match in t2 are shown their place outside the kingdom

Enemies in disguise: Avoid common faults

Beware the common traitors lurking in the shadows:

Double agents – conflicting table and column names: Dispatch aliases to avoid confusion.
Deserters – incorrect NULL handling: Make sure the WHERE clause checks accurately for NULL values left by LEFT JOIN.
Betrayers – unintended deletions: Practice DELETE queries on dummy data to avoid real-data casualties.