Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Statements and Clauses

SQL RETURNING clause

Reference for the SQL RETURNING clause: get the rows an INSERT, UPDATE or DELETE changed, read generated ids, and see the OUTPUT and RETURNING INTO variants.

RETURNING makes an INSERT, UPDATE or DELETE statement hand back rows, just as a SELECT would. You choose the columns or expressions, and you get one result row for each row the statement changed. It removes the need for a second query to read generated ids or the final values.

SQLite (3.35+) and PostgreSQL support it with this name. MySQL does not (MariaDB does for INSERT and DELETE); SQL Server calls it OUTPUT and Oracle uses RETURNING ... INTO.

SQL
INSERT INTO categories (name)
VALUES ('Garden')
RETURNING id, name;

Try it

SQL Getting the generated id back ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL RETURNING with UPDATE and DELETE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
INSERT INTO table_name (...) VALUES (...) RETURNING expression [AS alias] [, ...];
UPDATE table_name SET ... WHERE ... RETURNING expression [, ...];
DELETE FROM table_name WHERE ... RETURNING expression [, ...];
Part Description
RETURNING list Columns of the affected rows, or expressions over them. * returns all columns.
AS alias Optional name for an expression column.

Returns: one row per inserted, updated or deleted row. For UPDATE it shows the values after the change; for DELETE the values that were removed.

Dialect differences

Dialect Spelling
SQLite RETURNING on INSERT, UPDATE, DELETE (3.35+). Cannot contain aggregates or window functions; rows come back in no guaranteed order.
PostgreSQL RETURNING, also on MERGE from 17. Can use expressions and, with WITH, feed results into another statement.
MySQL Not available. Use LAST_INSERT_ID() and a follow-up SELECT.
MariaDB INSERT ... RETURNING (10.5+) and DELETE ... RETURNING (10.0+).
SQL Server OUTPUT inserted.col, deleted.col, placed before VALUES or WHERE. Can also write into a table variable.
Oracle RETURNING col INTO :variable in PL/SQL or from a client API.
SQL
-- SQL Server
UPDATE products
SET stock = stock + 20
OUTPUT inserted.id, inserted.stock
WHERE stock = 0;

-- Oracle (PL/SQL)
UPDATE products SET stock = stock + 20
WHERE id = 5
RETURNING stock INTO v_stock;

Usage notes

  • Read generated keys safely: RETURNING id gives the id of exactly the row you inserted, with no race against other sessions, unlike a global "last id" function.
  • Multi-row statements return multiple rows; applications must read them all.
  • Combine with upserts to see the final row, whether inserted or updated.
  • Triggers: values changed by AFTER triggers may not be visible in RETURNING.
  • Inside a transaction, the returned rows are only as permanent as the transaction; if you roll back, they never existed.
  • Used in application code, the statement is executed like a SELECT (use a query call, not an "execute").
  • Portable alternative: insert, then SELECT the row by a unique key.