What does adding RETURNING order_id to an INSERT statement produce?

## What the clause does Normally a DML statement is write-only from the client's point of view: `INSERT`, `UPDATE` and `DELETE` report how many rows they affected, and nothing else. If you need to know *what* the server stored — the key it generated, the timestamp its default filled in, the value a computed column derived — you have to ask again with a `SELECT`. A `RETURNING` clause removes that second step. You append it to the write statement with an output list shaped exactly like a `SELECT` list: ```sql INSERT INTO orders (customer_id, total) VALUES (42, 99.50) RETURNING order_id, created_at, total * 0.2 AS tax; ``` The statement performs the insert and, in the same execution, produces a result set. ## The shape of the result The result is a table, not a scalar. It has one row for every row the statement inserted (five rows for a five-row `VALUES` list, N rows for an `INSERT ... SELECT` that inserted N rows), and one column per expression in the output list. `RETURNING *` returns every column of the target table, which is handy for a round-trip of the complete stored row. Because of that, the statement must be executed as a query. In JDBC that means `executeQuery` (or `execute` plus `getResultSet`) rather than `executeUpdate`; in Python DB-API you call `cursor.fetchall()` after executing; in Go you use `Query` rather than `Exec`. Sending a `RETURNING` statement down an update-only API is the single most common first mistake: some drivers throw, others silently discard the rows. ## Which values you see The expressions are evaluated against the row **as stored**, not against the literals you supplied. That is the whole point. Concretely: - an identity or sequence-backed key that you did not supply comes back with its assigned value; - a column you omitted comes back with the default the server applied; - a generated/computed column comes back with its computed value; - if a BEFORE-style trigger rewrote a column on the way in, you see the rewritten value. So `INSERT INTO users (email) VALUES ('[email protected]') RETURNING user_id, created_at, status` tells you the key, the timestamp and the default status in one round trip. ## Why it matters Three practical wins. First, one network round trip instead of two, which matters a lot in a loop or a hot write path. Second, no ambiguity about *which* row you are reading back: the result belongs to the rows this statement wrote, so you do not have to invent a `WHERE` clause that re-identifies them (often impossible when the only unique value is the key the server just generated). Third, it composes: engines that allow a data-modifying statement inside a `WITH` clause let you feed the returned rows straight into another statement. ## Where it works, and how it is spelled `RETURNING` is not part of core ISO SQL — it is a vendor extension that several engines converged on. PostgreSQL and SQLite (from 3.35) support it on `INSERT`, `UPDATE` and `DELETE`; MariaDB supports it on some statement kinds depending on version. Oracle has `RETURNING ... INTO`, which writes into bind variables instead of producing a client result set. SQL Server spells the feature `OUTPUT`, using `inserted.` and `deleted.` pseudo-tables. MySQL has no `RETURNING` clause at all. Treat "my engine returns rows from an INSERT" as something to verify, not assume. ## Common mistakes Expecting `RETURNING` to echo back the values you sent — it echoes what was stored, which can differ. Expecting an update count as well as rows; you generally get the rows, and the row count is the number of rows in the result set. Assuming it works on any engine, then discovering the target deployment is MySQL. And putting business logic in the output list that would be clearer in the surrounding query — the clause is for reading back what the write produced, not a general-purpose reporting hook.