SQL RETURNING

What is the SQL RETURNING clause?

RETURNING immediately outputs specified columns from rows affected by INSERT, UPDATE, or DELETE.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RETURNING: PostgreSQL 8.2+, SQLite 3.35+, MySQL 8.0.19+, Oracle 10g (via RETURNING INTO), Firebird, IBM Db2, CockroachDB. SQL Server uses the analogous OUTPUT clause.

SQL RETURNING Full Explanation

The SQL RETURNING clause lets a DML statement (INSERT, UPDATE, DELETE) output data from the rows it just affected without requiring a separate SELECT. It is executed as part of the same transaction so the returned values reflect the final state of the modified rows. RETURNING can list one or more columns, expressions, or the wildcard *. You can alias returned columns, use computed expressions, and capture the result set in application code or a Common Table Expression (CTE).Benefits:- Saves a round-trip by combining write and read.- Guarantees consistency because the read happens inside the same statement lock scope.- Simplifies workflows like obtaining autogenerated primary keys, updated timestamps, or audit fields.Caveats:- Not all databases support it, or they implement it with different syntax (for example, SQL Server uses OUTPUT).- Large RETURNING result sets can increase network traffic.- Some ORMs enable it by default which may surprise users when performance degrades on bulk writes.

SQL RETURNING Syntax

INSERT INTO table_name (col1, col2)
VALUES (val1, val2)
RETURNING column_list;

UPDATE table_name
SET col1 = new_val
WHERE condition
RETURNING column_list;

DELETE FROM table_name
WHERE condition
RETURNING column_list;

SQL RETURNING Parameters

column_list (required) - list of columns or expressions to return, or * for all columns
output_alias (optional) - alias assigned to a returned column or expression

Example Queries Using SQL RETURNING

-- 1. Get autogenerated id on insert
INSERT INTO users (email)
VALUES ('ada@getgalaxy.io')
RETURNING id;

-- 2. Return changed rows after update
UPDATE accounts
SET status = 'inactive', updated_at = NOW()
WHERE last_login < CURRENT_DATE - INTERVAL '180 days'
RETURNING id, status;

-- 3. Capture deleted rows in a CTE for archival
WITH deleted AS (
  DELETE FROM sessions
  WHERE expires_at < NOW()
  RETURNING *
)
INSERT INTO session_archive SELECT * FROM deleted;

Expected Output Using SQL RETURNING

Each statement returns a result set containing the specified columns for every row it inserted, updated, or deleted
The calling client can fetch these rows like any SELECT result

Use Cases with SQL RETURNING

Fetch autogenerated primary keys right after insert
Return updated column values (e.g., new timestamps, counters)
Move deleted rows to an audit table in one atomic statement
Chain modifications inside CTEs without extra queries

Common Mistakes with SQL RETURNING

Assuming RETURNING is portable across all databases without checking dialect support
Forgetting to limit the returned columns, causing large result sets
Using RETURNING in bulk operations where the client framework cannot handle the volume
Mixing RETURNING with triggers that also select, leading to unexpected columns

First Introduced In

PostgreSQL 8.2 (2006)

Frequently Asked Questions

What databases support RETURNING?

PostgreSQL, SQLite (3.35+), MySQL (8.0.19+), Firebird, CockroachDB, and IBM Db2 support a RETURNING clause. Oracle provides RETURNING INTO and SQL Server offers the OUTPUT clause.

Can I use RETURNING with multiple rows?

Yes. The clause returns one row for every row affected. If you insert five rows, you will receive five rows in the result set unless you add LIMIT or a condition.

Is RETURNING transactional?

Absolutely. It executes in the same transaction and lock scope as the DML statement, guaranteeing consistency.

How do I capture RETURNING results in SQL?

You can fetch the result directly in your application code, assign it to a client variable, or wrap the DML in a CTE to reuse the output later in the same statement.