> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.
# RETURNING
> DQL clause for retrieving data from the documents affected by INSERT, UPDATE, DELETE, EVICT and TOMBSTONE statements
`RETURNING` is available in SDK 5.1 and later.
The optional `RETURNING` clause is accepted by the three DML (Data Manipulation Language) statements — [INSERT](/dql/insert), [UPDATE](/dql/update) and [DELETE](/dql/delete)/[EVICT](/dql/evict)/TOMBSTONE — and by no other statement.
A DML statement normally reports only the IDs of the documents it changed. Adding `RETURNING` turns it into a statement that returns a result set built from the affected documents, in the same form as a [SELECT](/dql/select) statement, so you can read the affected data back without issuing a second query.
## Documents Projected
Each statement projects the affected documents at a different point in its processing:
| Statement | Documents projected |
| ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| `INSERT` | The documents as written, after the insert. Documents skipped by `ON ID CONFLICT DO NOTHING` are not written, so are not projected. |
| `UPDATE` | The documents as they are after the `APPLY`, `SET` and `UNSET` mutators have been applied. |
| `DELETE`/`EVICT`/`TOMBSTONE` | The documents as they were immediately before removal. |
A statement populates either its result set (when `RETURNING` is present) or its list of mutated document IDs (when it is not), never both.
## Projection Syntax
`RETURNING` takes the same [projection](/dql/select#projections) syntax as a `SELECT` statement, with these differences:
* `DISTINCT` may not be specified. The word is taken to be a field name rather than a keyword.
* `RAW` projections are not supported.
* The unqualified `*` wildcard is only accepted as the sole element of the projection list; combining it with further elements is an error. A qualified `alias.*` has no such restriction.
* [Aggregates](/dql/select#aggregate-functions) are permitted and are evaluated over the whole set of affected documents; there is no `GROUP BY` clause. When any element of the projection is an aggregate, the remaining elements must not depend on the documents, otherwise an error is raised.
Default aliases are assigned, and uniqueness of aliases is required, exactly as for a `SELECT` projection.
## Examples
Return the complete documents that were inserted:
```sql DQL theme={null}
INSERT INTO cars DOCUMENTS ({"_id": "123", "color": "blue"})
RETURNING *
```
Return selected fields of the documents that were updated, with their post-update values:
```sql DQL theme={null}
UPDATE cars
SET color = 'red'
WHERE color = 'blue'
RETURNING _id, color
```
Return the documents that were deleted, as they were immediately before removal:
```sql DQL theme={null}
DELETE FROM cars
USE IDS '123', '456'
RETURNING *
```
Return an aggregate over the affected documents rather than the documents themselves:
```sql DQL theme={null}
DELETE FROM cars
WHERE year < 2000
RETURNING COUNT(*) AS removed
```
Use an expression and an alias, exactly as in a `SELECT` projection:
```sql DQL theme={null}
UPDATE cars
APPLY odometer INCREMENT BY 100
WHERE _id = '123'
RETURNING _id, odometer / 1.60934 AS mileage
```
## RETURNING and Legacy DML
`RETURNING` is implemented by the query engine's DML operators only. The legacy (non-operator-model) implementation does not support it, so a statement that combines the two is rejected with the error `RETURNING clause with legacy DML is not supported`.
Legacy handling is selected in either of two ways:
* the [`#disable_dml`](/dql/directives#disable_dml) directive on an individual statement, which disables `RETURNING` for that statement only; or
* the [`DQL_USE_LEGACY_DML`](/dql/alter-system#dql_use_legacy_dml) system parameter, which does the same for every statement.
The same restriction applies to the rest of the syntax that only the operator model implements: `USE IDS` on `UPDATE` and `DELETE`/`EVICT`/`TOMBSTONE`, and [`INSERT` sourced from a `SELECT`](/dql/insert#insert-from-a-select-statement).
`DQL_USE_LEGACY_DML` defaults to `false` in the Edge SDK (small peer) and `true` on Ditto Server (big peer). `RETURNING` is therefore available by default in the SDK, and on Ditto Server only where that parameter has been set to `false`.
## See Also
* [INSERT](/dql/insert) - Inserting documents
* [UPDATE](/dql/update) - Modifying documents
* [DELETE](/dql/delete) - Permanently removing documents
* [EVICT](/dql/evict) - Removing documents locally
* [SELECT](/dql/select#projections) - Projection syntax and aggregates
* [Directives](/dql/directives#disable_dml) - The `#disable_dml` directive
## Related topics
- [DELETE](/dql/delete.md)
- [EVICT](/dql/evict.md)
- [UPDATE](/dql/update.md)