RETURNING

DML
Buy Me A Coffee

Return column values from rows affected by an INSERT, UPDATE, or DELETE — without a separate SELECT round-trip.

Returns columns from modified rows (INSERT, UPDATE, DELETE) as part of the statement result, avoiding a separate SELECT to fetch generated values. PostgreSQL, SQLite, and SQL Server support it; MySQL does not natively.

Compatibility

Filter by Database
SQL RETURNING Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Not Supported - Not supported. Use LAST_INSERT_ID() for the auto-increment key after INSERT; no equivalent for UPDATE or DELETE.
PostgreSQL Native 8.2 Full RETURNING on INSERT, UPDATE, and DELETE. Can return any expression. Also supported in MERGE (PG 15+). Returns post-change values for UPDATE, deleted values for DELETE.
SQL Server Native 2005 OUTPUT clause with INSERTED.* (post-change) and DELETED.* (pre-change). Different syntax but functionally equivalent. Can redirect output into a table variable with OUTPUT ... INTO.
Oracle Native all RETURNING ... INTO :bind_var. Requires PL/SQL host/bind variables — cannot return a bare result set in plain SQL. Effectively PL/SQL-only; single-row practical for INSERT.
SQLite Native 3.35.0 RETURNING clause added in SQLite 3.35.0 (March 2021). Syntax matches PostgreSQL.

Details

MySQL's lack of RETURNING for UPDATE/DELETE is a common pain point. Oracle's implementation requires PL/SQL bind variables, making it impractical in plain SQL. SQL Server's OUTPUT clause is fully equivalent but uses different keywords.

Standard Syntax

INSERT INTO users (name) VALUES ('Alice') RETURNING id, created_at; UPDATE orders SET status = 'shipped' WHERE id = 42 RETURNING *; DELETE FROM sessions WHERE expired_at < NOW() RETURNING session_id;

Version Support

MySQL: Not supported PostgreSQL: Native since 8.2 SQL Server: Native since 2005 Oracle: Native in all listed versions SQLite: Native since 3.35.0

Per-Database Syntax & Notes

MySQL Alternative syntax

Not supported. Use LAST_INSERT_ID() for the auto-increment key after INSERT; no equivalent for UPDATE or DELETE.

INSERT INTO tbl (name) VALUES ('Alice'); SELECT LAST_INSERT_ID(); -- only for the generated key

PostgreSQL Native syntax

Full RETURNING on INSERT, UPDATE, and DELETE. Can return any expression. Also supported in MERGE (PG 15+). Returns post-change values for UPDATE, deleted values for DELETE.

INSERT INTO users (name) VALUES ('Alice') RETURNING id, created_at; UPDATE orders SET status = 'shipped' WHERE id = 42 RETURNING *; DELETE FROM sessions WHERE expired_at < NOW() RETURNING session_id;

SQL Server Native syntax

OUTPUT clause with INSERTED.* (post-change) and DELETED.* (pre-change). Different syntax but functionally equivalent. Can redirect output into a table variable with OUTPUT ... INTO.

INSERT INTO users (name) OUTPUT INSERTED.id, INSERTED.name VALUES ('Alice'); UPDATE orders SET status = 'shipped' OUTPUT INSERTED.*, DELETED.status WHERE id = 42; DELETE FROM sessions OUTPUT DELETED.session_id WHERE expired_at < GETDATE();

Oracle Native syntax

RETURNING ... INTO :bind_var. Requires PL/SQL host/bind variables — cannot return a bare result set in plain SQL. Effectively PL/SQL-only; single-row practical for INSERT.

-- PL/SQL context only: INSERT INTO users (name) VALUES ('Alice') RETURNING id INTO :new_id; -- :new_id is a bind variable in application code

SQLite Native syntax

RETURNING clause added in SQLite 3.35.0 (March 2021). Syntax matches PostgreSQL.

INSERT INTO users (name) VALUES ('Alice') RETURNING id; UPDATE orders SET status = 'shipped' WHERE id = 42 RETURNING *;