skip to content

Which equivalents of RETURNING exist in engines that lack the clause?

level: seniorimportance: nice to knowfreq 28%

answer

  1. not every engine spells it the same way
  2. one camp uses a different keyword entirely
  3. one major engine has no such clause
  4. OUTPUT with inserted and deleted
  5. bind variables instead of a result set

basics

~20 s

RETURNING is not core ISO SQL. PostgreSQL and SQLite spell it RETURNING, SQL Server uses OUTPUT with inserted and deleted pseudo-tables, Oracle uses RETURNING ... INTO bind variables, and MySQL has none — LAST_INSERT_ID() is its fallback.

solid answer

~50 s

There is no portable spelling, so a data-access layer that assumes one is not portable. PostgreSQL and SQLite (3.35+) accept `RETURNING` on `INSERT`, `UPDATE` and `DELETE`; MariaDB supports it on some statement kinds depending on version. SQL Server has `OUTPUT`, which reads the `inserted` and `deleted` pseudo-tables and can stream rows to the client or into a table with `OUTPUT ... INTO`. Oracle's `RETURNING ... INTO` writes into bind variables rather than producing a result set, and needs `BULK COLLECT INTO` for multiple rows. MySQL has no such clause at all; you read the generated key back with `LAST_INSERT_ID()`, which is session-scoped and reports the first key of a multi-row insert. Db2 exposes the standard's data-change delta tables: `SELECT * FROM FINAL TABLE (INSERT INTO ...)`. Practically: hide the difference behind one statement per dialect, or fall back to the driver's generated-keys API.

code

sql · 3 lines
sql
-- PostgreSQL / SQLite 3.35+
INSERT INTO orders (customer_id, total) VALUES (42, 99.50)
RETURNING order_id, created_at;

go deeper

for a junior

Know that reading rows back from a write is an engine-specific feature, not something every SQL database offers.

for a middle

Name the two main spellings — RETURNING and OUTPUT — and explain that Oracle assigns into bind variables while MySQL offers no such clause at all.

for a senior

Show how the data-access layer absorbs the difference: per-dialect write statements or the driver's generated-keys API, and never assume a write returns a result set.

for a principal

Own the engine-portability contract for the codebase: which databases are supported, whether result-returning DML may be assumed, and what that implies for key generation strategy.

## No portable spelling exists "Give me back the rows I just wrote" is a universally useful capability that the SQL standard never blessed as a clause. The standard's route is **data-change delta tables** — you query the result of a modification as if it were a table: ```sql -- Db2 SELECT order_id, created_at FROM FINAL TABLE (INSERT INTO orders (customer_id, total) VALUES (42, 99.50)); ``` Very few engines implement that spelling. The rest converged, independently, on two different syntaxes plus one non-answer. ## The RETURNING family PostgreSQL is the reference implementation: `RETURNING` attaches to `INSERT`, `UPDATE` and `DELETE`, takes a full output list including `*` and expressions, and yields a client result set. SQLite adopted the same clause in version 3.35. MariaDB has it too, but check which statement kinds and which version before relying on it — support arrived piecewise and is narrower than PostgreSQL's. ```sql INSERT INTO orders (customer_id, total) VALUES (42, 99.50) RETURNING order_id, created_at; ``` ## SQL Server's OUTPUT SQL Server's equivalent is the `OUTPUT` clause, and it is shaped differently in two ways. It is written before the `WHERE` clause rather than at the end, and it projects from two pseudo-tables rather than from the row directly: ```sql INSERT INTO orders (customer_id, total) OUTPUT inserted.order_id, inserted.created_at VALUES (42, 99.50); ``` `inserted` is the post-modification image and `deleted` the pre-modification one, so an `UPDATE` can project both. `OUTPUT ... INTO target (cols)` sends the rows into a table instead of to the caller — useful for audit and archive writes. ## Oracle's RETURNING ... INTO Oracle uses the keyword `RETURNING`, but it does not produce a result set. It assigns into bind variables or PL/SQL variables: ```sql INSERT INTO orders (customer_id, total) VALUES (42, 99.50) RETURNING order_id INTO :new_order_id; ``` That difference matters to the client code: you bind an out-parameter rather than iterating a cursor. For multi-row statements, the PL/SQL form is `RETURNING ... BULK COLLECT INTO` a collection. So "Oracle has RETURNING" is true as syntax and misleading as a programming model. ## Engines without the feature MySQL has no `RETURNING` clause. The customary way to learn a generated key is the function `LAST_INSERT_ID()`, which reports the value produced for the current session — it is not affected by other connections' inserts. Two properties trip people up: for a multi-row insert it reports the key of the **first** row, not the last; and it tells you about auto-increment values only, not about defaults or generated columns, which still need a `SELECT`. Most drivers expose the same value through a generated-keys API, which is why portable frameworks tend to use that API instead of any SQL clause. ## Writing code that must span engines Three workable strategies. First, keep the write statement per dialect: one small function per engine that performs the insert and yields the key, with everything above it engine-agnostic. Second, use the driver's generated-keys facility, which is the closest thing to a common denominator for the narrow case of "just give me the identity value" — at the cost of not being able to read back defaults, computed columns or multiple rows. Third, when the target set is fixed and modest, generate keys client-side (for example a UUID) so nothing needs reading back at all; that is a schema decision with its own consequences, not a free win. What you should not do is assume a result set from a write. Code that calls the query path on an `INSERT` works beautifully on PostgreSQL and fails on MySQL, and the failure surfaces in whichever deployment you tested least. ## What an interviewer is checking That you know the feature is an extension rather than standard SQL; that you can name at least the `RETURNING` and `OUTPUT` camps and describe how their shapes differ; and that you can say what changes in the application layer when the clause is absent. Naming every engine is not the point — recognising that the portability boundary exists, and designing the data-access layer around it, is.

  • How does Oracle's RETURNING ... INTO differ from PostgreSQL's RETURNING in the client code?
    PostgreSQL produces a result set you iterate like a query. Oracle assigns into bind or PL/SQL variables, so the client registers out-parameters instead; multi-row statements need the PL/SQL `BULK COLLECT INTO` form rather than a cursor over returned rows.
  • What are the pitfalls of relying on LAST_INSERT_ID() in MySQL?
    It reports auto-increment values only, so defaults and generated columns still need a SELECT, and for a multi-row insert it gives the key of the first inserted row rather than the last. It is scoped to the session, so other connections do not perturb it.

  • Claims RETURNING is standard SQL supported everywhere
  • Says MySQL supports INSERT ... RETURNING
  • Treats Oracle's RETURNING INTO as producing a result set
  • Thinks LAST_INSERT_ID() returns the last row of a multi-row insert
  • Assumes OUTPUT is written at the end like RETURNING

RETURNING is a vendor extension, not core ISO SQL. SQLite has it from 3.35; MariaDB's support arrived per statement kind by version; MySQL 8.x has no RETURNING clause.

context