What is the RETURNING clause in SQL?

A database is a collection of structured data stored in an organized manner. Using SQL, we can perform multiple operations such as creating tables, inserting data into tables, modifying the records, and deleting the existing data from the tables. The basic clauses in SQL are:

• SELECT: Retrieve data from tables

• FROM: Specify data source tables

• WHERE: Filter rows based on conditions

• ORDER BY: Sort result rows

• GROUP BY: Group rows for aggregate functions

• JOIN: Combine data from multiple tables

• INSERT INTO: Add new rows to a table

• UPDATE: Modify existing data in a table

The RETURNING clause in SQL

The RETURNING clause in SQL is used to retrieve values from a data modification statement, such as INSERT, UPDATE, or DELETE after the statement has been executed. This can be particularly useful when we want to know the values that were actually inserted, updated, or deleted as a result of the operation.

Code explanation

• Line 1: The <DML Operation> represents a Data Manipulation Language (DML) operation, which can be one of the following — INSERT, UPDATE, or DELETE — for the RETURNING clause. These operations are used to manipulate data in the database.

• Line 2: The “-- Other optional statements” comment indicates that we can include additional SQL statements before the RETURNING clause if needed for any specific use case.

• Line 3: RETURNING is the keyword that is used for the RETURNING clause. The <Returned_Column(s)> represents the column or columns whose values we want to retrieve, after performing the DML operation. We can specify the column names that we want to return from the affected rows.

Importance of the RETURNING clause

While we can achieve similar results using separate SELECT statements, the RETURNING clause offers several advantages:

While we can certainly achieve similar results by performing a SELECT statement after a data modification, using the RETURNING clause streamlines the process. Using the RETURNING clause also provides a more efficient and integrated way to retrieve specific data related to the modified rows. It’s a powerful feature that enhances both the performance and the clarity of the SQL code when working with data modifications.

Code example

Let’s look at the sections below, which show a specific example of the RETURNING clause with the INSERT, UPDATE, and DELETE commands.

First of all, we need to set up the database.

Setting up the database

We’ll use a simple database named Employees having four columns, the emp_id, emp_name, dept, and date_of_joining.

Code explanation

• Lines 1–7: A table named the Employees is created with four columns, the emp_id (serialSERIAL is a keyword used to create an auto-incrementing integer column. When you define a column as SERIAL, PostgreSQL automatically generates a unique integer value for each new row inserted into the table. This is commonly used for primary key columns to ensure that each row has a unique identifier., primary key), emp_name, dept, and date_of_joining.

• Lines 9–26: A few records are inserted into the Employees table using the INSERT INTO command.

• Line 28: Displays the data using the SELECT statement.

The INSERT command

In the following SQL operation, we’ll demonstrate the process of inserting a new employee's information into the Employees table. This query will include the employee’s name, department, and date of joining. Additionally, we’ll utilize the RETURNING clause to retrieve the newly inserted record. Press the “Run” button below to see the output:

Code explanation

• Lines 1–2: The query inserts the employee’s name “Ayesha Sultan,” department as “HR,” and the joining date, which is “2023-08-23.”

• Line 3: After the insertion, the query retrieves the newly inserted record using the RETURNING clause.

The UPDATE command

In the following SQL operation, we’ll demonstrate the process of updating an existing employee’s information in the Employees table. Let’s suppose the date of joining of Ayesha Sultan is accidentally recorded as “2023-08-23,” whereas it should have been correctly noted as “2023-08-12.” We’ll fix the date using the UPDATE command. Additionally, we’ll utilize the RETURNING clause to retrieve the modified record. Press the “Run” button to see the output:

Code explanation

• Lines 1–3: The query modifies the employee’s record of “Ayesha Sultan,” whose emp_id is 16, and sets the date of joining to “2023-08-12.”

• Line 4: After the modification, the query retrieves the modified record using the RETURNING clause.

The DELETE command

In the following SQL operation, we’ll demonstrate the process of deleting an existing employee’s information in the Employees table. Imagine a scenario where “Ayesha Sultan,” a key member of the company, has made the decision to part ways with the company. In accordance with this transition, we’ll utilize the DELETE command to remove her record from the database. Additionally, we’ll utilize the RETURNING clause to retrieve the deleted record. Press the “Run” button to see the output:

Code explanation

• Lines 1–2: The query deletes the employee’s record of “Ayesha Sultan,” whose emp_id is 16.

• Line 3: After the deletion, the query retrieves the deleted record using the RETURNING clause.

Conclusion

Our exploration of the RETURNING clause in SQL has revealed its valuable role in optimizing and streamlining database operations. By seamlessly integrating data modification and retrieval within a single query, the RETURNING clause reduces the need for additional round trips to the database server. This results in enhanced efficiency and performance. This powerful feature also ensures data consistency and enables us to obtain specific values resulting from the INSERT, UPDATE, or DELETE commands, while maintaining code readability. As we conclude our discussion, it’s evident that the RETURNING clause stands as a versatile tool for enhancing the precision and effectiveness of our database interactions.