INSERT RETURNING in MariaDB

MariaDB, an open–source relational database management system, continues to evolve with new features and enhancements. One such feature introduced in version 10.5 is the INSERT…RETURNING statement, which offers a convenient and efficient way to insert data into a table and retrieve the inserted rows in a single operation.

This feature simplifies queries and saves time by eliminating the need for separate insert and select statements.

In this article, we will learn about everything about the INSERT…RETURNING with the help of examples and so on.

What is Returned by INSERT?

The INSERT...RETURNING statement combines both INSERT and SELECT statements. It inserts one or more rows into a table and then returns a result containing the values of inserted rows depending upon the parameter.

After the RETURNING keyword, we can provide the column list or SELECT expression that specifies the columns to be returned. We can also use an asterisk (*) to get all the columns of the inserted rows.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING [ * | column_list | SELECT expression ];

Explanation:

• column_list: You can directly provide the comma-separated list of columns whose values you want to retrieve from the inserted rows.
  
• SELECT expression: You can also provide only a SELECT expression that specifies the values to be returned. The SELECT keyword is not required, specify only the expression.

Examples of INSERT…RETURNING

To understand INSERT…RETURNING in MariaDB we need a table on which we will perform various operations and queries. Here we will consider a table called users which contains id, username, email, and created_at as Columns.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Output:

Creation of users table

Explanation: The query creates a table named users with columns id, username, email and created_at having respective constraints.

Example 1: Returning All Columns

The below query inserts three rows and also returns all the inserted rows with respect to all the columns of the table.

INSERT INTO users (username, email)
VALUES ('john.doe', 'john.doe@example.com'),
       ('jane.smith', 'jane.smith@example.com'),
       ('alice.lee', 'alice.lee@example.com')
RETURNING *;

Output:

Returning All Columns

Explanation: The query inserts three rows with username and email values but returns all these rows with all the columns of the table including id and created_at.

Example 2: Returning Specific Columns

The below query inserts three rows and also returns all the inserted rows with respect to specified columns of the table.

INSERT INTO users (username, email)
VALUES ('david.kim', 'david.kim@example.com'),
       ('maria.garcia', 'maria.garcia@example.com'),
       ('michael.chen', 'michael.chen@example.com')
RETURNING id, username;  

Output:

Returning Specific Columns

Explanation: The query inserts three rows with username and email values but returns all these rows with only the id and username columns.

Example 3: Returning with Select Expression

The below query inserts three rows and also returns all the inserted rows with columns that satisfies the SELECT expression.

INSERT INTO users (username, email)
VALUES ('sarah.nguyen', 'sarah.nguyen@example.com'),
       ('peter.johnson', 'peter.johnson@example.com'),
       ('emma.white', 'emma.white@example.com')
RETURNING username AS user_name, CONCAT('User ID:', id) AS user_id_message;

Output:

Returning with Select Expression

Explanation: The query inserts three rows with username and email values but returns all these rows with values of username columns named as user_name and values of id column named as user_id_message concatenated with User ID: string.

Conclusion

The INSERT…RETURNING statement in MariaDB provides a powerful and efficient way to insert data into a table and retrieve the inserted rows in a single operation. This feature simplifies queries, saves time and enhances the overall productivity of developers and database administrators. Whether you need to retrieve all columns, specific columns or custom expressions, INSERT…RETURNING offers flexibility and convenience, making it a valuable addition to your MariaDB.