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.
INSERT INTO categories (name) VALUES ('Garden') RETURNING id, name;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 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 idgives 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
AFTERtriggers may not be visible inRETURNING. - 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
SELECTthe row by a unique key.