SQL · Theory

DELETE JOIN

Theory

65/439

DELETE JOIN

MySQL allows DELETE s FROM students s JOIN marks m ON s.roll = m.roll WHERE m.score = 0; delete students who have a zero paper. Multi-table DELETE needs the alias after DELETE.

Safer — SELECT first, then delete by PK list. FKs still apply.

Trap — DELETE FROM students JOIN … with SQL Server syntax. MySQL wants DELETE alias FROM … JOIN.

On the example next to this theory: DELETE JOIN — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

DELETE s FROM students s JOIN marks m ON … WHERE …

DELETE JOIN — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

DELETE JOIN — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Short notes

DefMulti-table DELETE uses JOIN to pick rows.
RuleDELETE alias FROM t alias JOIN …
TrapWrong dialect syntax. Skipping a preview SELECT.

SQL · Theory

DELETE JOIN

← All stacks

Theory

65/439

DELETE JOIN

MySQL allows DELETE s FROM students s JOIN marks m ON s.roll = m.roll WHERE m.score = 0; delete students who have a zero paper. Multi-table DELETE needs the alias after DELETE.

Safer — SELECT first, then delete by PK list. FKs still apply.

Trap — DELETE FROM students JOIN … with SQL Server syntax. MySQL wants DELETE alias FROM … JOIN.

On the example next to this theory: DELETE JOIN — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

DELETE s FROM students s JOIN marks m ON … WHERE …

DELETE JOIN — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

DELETE JOIN — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Short notes

DefMulti-table DELETE uses JOIN to pick rows.
RuleDELETE alias FROM t alias JOIN …
TrapWrong dialect syntax. Skipping a preview SELECT.

DELETE JOIN

DELETE JOIN — sample query

Short notes

Questions

DELETE JOIN

DELETE JOIN — sample query

Short notes

Questions