DEV Community

Alessio Michelini
Alessio Michelini

Posted on • Updated on

The RETURNING clause in SQL

This is a little tip I learned today from a colleague, and it's something so simple and so effective that I still wonder why it never popped in my radar for so many years.
But essentially the RETURNING is a clause you can use after an INSERT/UPDATE/DELETE statement, to simply return the data from the previous query.

For example, in an INSERT statement, you can do the following:

INSERT INTO customers (name, email)
VALUES ('John Doe', 'johndoe@email.com')
RETURNING customer_id;
Enter fullscreen mode Exit fullscreen mode

This query would return the customer_id value of the newly inserted row.

True that if you use some libraries, they will do that for you, so maybe the ID of a row it could be just there with the response, like it does with the mysql2 npm package in Node.js, BUT, if you need to know other information, like something else that is autogenerated, you can use the RETURNING clause to get that on your SQL response.

You could also return the entire row by using RETURNING *, for example:

INSERT INTO customers (name, email)
VALUES ('John Doe', 'johndoe@email.com')
RETURNING *;
Enter fullscreen mode Exit fullscreen mode

Will return the entire data of the row you just inserted.

And of course it will work* the same way for UPDATE/DELETE, for example:

-- Update
UPDATE customers
SET email = 'newemail@email.com'
WHERE customer_id = 10
RETURNING name, email;

-- Delete
DELETE FROM customers
WHERE customer_id = 10
RETURNING name, email;
Enter fullscreen mode Exit fullscreen mode

* On PostgreSQL, but not MariaDB/MySQL

Update 25/10/2023

It turns out that while the RETURNING clause works just fine with PostgreSQL, MySQL/MariaDB have only partial support for it.
It still works for the INSERT and DELETE statements, but not for the UPDATE.
It does work for the REPLACE statement though, as described on the MariaDB docs.

Stackoverflow thread about that issue

Top comments (2)

Collapse
 
mortylen profile image
mortylen

It is a very useful thing if you are querying a database from an external source, for example from a client application, and you want to know what data was actually inserted. Especially if the SQL table generates and modifies some data automatically.

Collapse
 
koas profile image
Koas

Omg! I had never heard of this, thanks!