RETURNINGDML
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

Show:
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 ✓ Supported 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 ✓ Supported 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 ✓ Supported 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 ✓ Supported 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: Since 8.2 SQL Server: Since 2005 Oracle: Since all SQLite: Since 3.35.0

Per-Database Syntax & Notes

MySQL

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

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

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

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

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 *;