OUTPUT / RETURNING INTO VARIABLE SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

Capturing values from rows affected by an INSERT, UPDATE, or DELETE into a variable or result set within the same statement.

SQL Server and Sybase equivalent of RETURNING, capturing values from modified rows. Useful for getting identity values, computed columns, or deleted row data immediately after an INSERT/UPDATE/DELETE operation.

Compatibility

Details

Database System	Support Status	Since Version	Notes
MySQL	✓ Supported	5.0	No OUTPUT/RETURNING clause. Use LAST_INSERT_ID() after INSERT to retrieve the auto-increment key. Multi-row capture requires triggers or application logic.
PostgreSQL	✓ Supported	8.2	RETURNING clause on INSERT, UPDATE, and DELETE returns a full result set. Works in plain SQL and inside PL/pgSQL with SELECT INTO or FOR loop.
SQL Server	✓ Supported	2005	OUTPUT clause on INSERT, UPDATE, DELETE, and MERGE. Can write to a table variable (OUTPUT INTO @tbl) or return rows to the client. Exposes inserted and deleted virtual tables.
Oracle	✓ Supported	8i	RETURNING...INTO in PL/SQL captures scalar or collection values from INSERT/UPDATE/DELETE. For bulk operations use RETURNING BULK COLLECT INTO.
SQLite	✓ Supported	3.35.0	RETURNING clause on INSERT, UPDATE, and DELETE added in 3.35.0 (2021). Returns a result set; no direct INTO variable support in SQL.

Capturing DML output inline is invaluable for audit logging, chained operations, and avoiding a second SELECT round-trip. PostgreSQL and SQLite share the RETURNING syntax and semantics. SQL Server's OUTPUT clause is the most powerful: it can route output to a table variable via OUTPUT INTO, enabling buffered multi-step operations, and it exposes both the pre-change (deleted) and post-change (inserted) images in a single UPDATE statement. Oracle's RETURNING INTO is PL/SQL only — it doesn't work in plain SQL scripts. MySQL has no equivalent clause at all; LAST_INSERT_ID() covers only the auto-increment key case, not arbitrary column values or UPDATE/DELETE captures. SQLite's RETURNING is recent (2021); target SQLite version carefully if deploying to embedded contexts.

Standard Syntax

Version Support

Per-Database Syntax & Notes

MySQL

No OUTPUT/RETURNING clause. Use LAST_INSERT_ID() after INSERT to retrieve the auto-increment key. Multi-row capture requires triggers or application logic.

INSERT INTO orders (item) VALUES ('widget'); SELECT LAST_INSERT_ID() AS new_id;

PostgreSQL

RETURNING clause on INSERT, UPDATE, and DELETE returns a full result set. Works in plain SQL and inside PL/pgSQL with SELECT INTO or FOR loop.

INSERT INTO orders (item) VALUES ('widget') RETURNING id, created_at; -- Capture into variable in PL/pgSQL: INSERT INTO orders (item) VALUES ('widget') RETURNING id INTO v_id;

SQL Server

OUTPUT clause on INSERT, UPDATE, DELETE, and MERGE. Can write to a table variable (OUTPUT INTO @tbl) or return rows to the client. Exposes inserted and deleted virtual tables.

-- Capture into table variable: DECLARE @ids TABLE (id INT); INSERT INTO orders (item) OUTPUT inserted.id INTO @ids VALUES ('widget'); SELECT * FROM @ids; -- Return to client: UPDATE orders SET status = 'closed' OUTPUT inserted.id, deleted.status WHERE status = 'open';

Oracle

RETURNING...INTO in PL/SQL captures scalar or collection values from INSERT/UPDATE/DELETE. For bulk operations use RETURNING BULK COLLECT INTO.

DECLARE v_id NUMBER; BEGIN INSERT INTO orders (item) VALUES ('widget') RETURNING id INTO v_id; DBMS_OUTPUT.PUT_LINE('new id: ' || v_id); END;

SQLite

RETURNING clause on INSERT, UPDATE, and DELETE added in 3.35.0 (2021). Returns a result set; no direct INTO variable support in SQL.

INSERT INTO orders (item) VALUES ('widget') RETURNING id, created_at; DELETE FROM orders WHERE status = 'old' RETURNING id;