How to use SELECT Statement in the VALUES List In MySQL

SELECT statement in VALUES list allows to insert multiple rows in a single INSERT INTO statement.

In this example, we have to table employees and departments and we will insert data into a new table employee_department_mapping by combining information from both tables.

When we use VALUES keyword with a subquery, we need to make sure that the subquery returns only one column. In our case, we have two columns employee_id and department_id in each subquery. To address this issue, we can use the SELECT statement directly without the VALUES keyword.

Query:

INSERT INTO employee_department_mapping (employee_id, department_id)
SELECT employee_id, department_id
FROM (
    SELECT employee_id, 1 AS department_id FROM employees WHERE salary > 55000
    UNION ALL
    SELECT employee_id, 2 AS department_id FROM employees WHERE salary <= 55000
    UNION ALL
    SELECT employee_id, 3 AS department_id FROM employees WHERE salary > 60000
) AS subquery;

Output:

MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table.

INSERT INTO SELECT statement is considered as a part of Data Manipulation Language (DML). DML operations deal with the manipulation of data stored in the database. The INSERT INTO SELECT statement in MySQL offers a powerful mechanism for transferring and manipulating data between tables. In this article, we will learn about What INSERT INTO SELECT statement with its syntax and example.