Delete Rows Using A Join Condition

Remove rows from one table based on matching rows in another table.

Created Apr 30, 2026 Last updated Apr 30, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Delete activity logs that belong to disabled users

Logs for user 2 are deleted because that user is disabled. The active user's log remains.

MySQL MariaDB SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, status VARCHAR(20));

CREATE TABLE activity_logs (id INT, user_id INT, message VARCHAR(50));

INSERT INTO
  users
VALUES
  (1, 'active'),
  (2, 'disabled');

INSERT INTO
  activity_logs
VALUES
  (10, 1, 'login'),
  (11, 2, 'login'),
  (12, 2, 'export');

Validated query

SQL

DELETE l
FROM
  activity_logs l
  JOIN users u ON u.id = l.user_id
WHERE
  u.status = 'disabled';

SELECT
  id,
  user_id,
  message
FROM
  activity_logs
ORDER BY
  id;

Expected result

id	user_id	message
10	1	login

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, status VARCHAR(20));

CREATE TABLE activity_logs (id INT, user_id INT, message VARCHAR(50));

INSERT INTO
  users
VALUES
  (1, 'active'),
  (2, 'disabled');

INSERT INTO
  activity_logs
VALUES
  (10, 1, 'login'),
  (11, 2, 'login'),
  (12, 2, 'export');

Validated query

SQL

DELETE FROM activity_logs l USING users u
WHERE
  u.id = l.user_id
  AND u.status = 'disabled';

SELECT
  id,
  user_id,
  message
FROM
  activity_logs
ORDER BY
  id;

Expected result

id	user_id	message
10	1	login

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, status TEXT);

CREATE TABLE activity_logs (id INT, user_id INT, message TEXT);

INSERT INTO
  users
VALUES
  (1, 'active'),
  (2, 'disabled');

INSERT INTO
  activity_logs
VALUES
  (10, 1, 'login'),
  (11, 2, 'login'),
  (12, 2, 'export');

Validated query

SQL

DELETE FROM activity_logs
WHERE
  EXISTS (
    SELECT
      1
    FROM
      users
    WHERE
      users.id = activity_logs.user_id
      AND users.status = 'disabled'
  );

SELECT
  id,
  user_id,
  message
FROM
  activity_logs
ORDER BY
  id;

Expected result

id	user_id	message
10	1	login

SQLite uses EXISTS instead of joined DELETE syntax.

Useful when

Where this command helps.

deleting child rows for archived parent records
removing records matched by a lookup table

Explanation

What the command is doing.

A joined delete is useful when the rows to remove are identified by related-table data. MySQL and MariaDB use DELETE target FROM target JOIN .... SQL Server also supports DELETE alias FROM ... JOIN .... PostgreSQL uses DELETE FROM target USING other_table. SQLite lacks joined DELETE syntax, so use a subquery with EXISTS instead.

Delete Rows Using A Join Condition

Delete activity logs that belong to disabled users

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.