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

## 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.