DELETE ... RETURNING

Remarks

Supported on both rowstore and columnstore tables.

Supported in both single- and multi-host deployments.

Works for both rowstore and columnstore tables, but very large deletes may still be subject to the same memory and performance considerations as standard DELETE (for example, use LIMIT in batches when deleting many rows from narrow rowstore tables).

The RETURNING clause accepts only * or column names (with optional aliases).

Expressions such as t.a+1, t.b*2, t.b+t.c in the RETURNING list are not supported.

DELETE ... RETURNING produces a result set that clients consume the same way as a SELECT result:

• Each row in the result set corresponds to a deleted row.

• The columns of the result set are exactly those listed in the RETURNING clause, in the specified order.

• If you use * all columns from the deleted table are returned.

• Column names in the result set reflect any aliases specified in the RETURNING clause.

The data visible in the RETURNING result set corresponds to the state of each row at the moment it is deleted.

For DELETE, all rows that appear in the result set are actually deleted from the table once the transaction commits.

Integration with Client Libraries - Because DELETE ... RETURNING uses the same protocol shape as SELECT (metadata + rows), client drivers and ORMs that support "DML with RETURNING" can consume the returned rows as a standard result set.

Any DELETE that involves more than one table (joins, multi-table deletes, or subselects in the target for returning multiple tables) is not supported with RETURNING.

Not supported in stored procedures or CTEs.

Examples

Example 1: The following example deletes expired products and returns all deleted rows for logging or auditing. The result set contains one row per deleted product, with all columns from the table PRODUCTS.

DELETE FROM products
  WHERE expiration_date <= CURDATE()
RETURNING *;

Example 2: The following example deletes a limited number of events. The LIMIT applies both to the number of rows deleted and the rows returned (similar to UPDATE ... RETURNING with LIMIT). The result set includes at most 100 rows, each with ID and CREATED_AT column values.

DELETE FROM events
  WHERE created_at < '2026-01-01'
    LIMIT 100
RETURNING id, created_at;

Example 3: The following example deletes specific employees and return selected columns.

DELETE FROM employees
  WHERE name IN ('Diana', 'Frank')
RETURNING id AS employee_id, name, salary;

Example 4: The following example deletes all employees in the 'Research' team and returns only the columns needed by the application, with aliases that match the client-side model.

DELETE FROM employees
  WHERE team = 'Research'
RETURNING name AS employee_name, salary, manager_id AS manager;

Example 5: The following example shows the traditional pattern vs. DELETE ... RETURNING. Without RETURNING, an application that needs to determine which rows were deleted typically runs two statements:

BEGIN;

SELECT *FROM products
  WHERE expiration_date <= CURDATE()FOR UPDATE;

DELETE FROM products
  WHERE expiration_date <= CURDATE();

COMMIT;

With DELETE ... RETURNING, the same workflow becomes a single statement, thereby avoids scanning the table twice and reduces the risk of anomalies related to re-identifying rows (e.g. phantom reads), since the delete and data retrieval happen atomically in a single statement.

DELETE FROM products
  WHERE expiration_date <= CURDATE()
RETURNING *;