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:

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:

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:

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:

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.

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.