This article explores the use of subqueries in INSERT statements with detailed examples to illustrate their utility.
What is a Subquery?
A subquery is a query within another SQL query and is embedded within the INSERT, SELECT, UPDATE, or DELETE statements. When placed in an INSERT statement, a subquery can:
1. Retrieve data from one or more tables.
2. Populate the target table based on specific criteria.
3. Combine data from multiple sources into a single operation.
Syntax of Subqueries in INSERT
The basic syntax for a subquery in an INSERT statement is as follows:
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
Types of Subqueries in INSERT
Simple Subqueries: Insert data based on a straightforward selection.
Correlated Subqueries: Insert data based on a relationship between the target and source tables.
Subqueries with Joins: Combine data from multiple tables into the target table.
Simple Subqueries
Simple subqueries in INSERT statements are used to insert data directly derived from another table.
Example: Simple Subquery in INSERT
Consider two tables, employees and new_employees:
Table: employees
| employee_id | employee_name | department_id |
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 10 |
Table: new_employees
| employee_id | employee_name | department_id |
Goal: Insert all rows from employees into new_employees.
INSERT INTO new_employees (employee_id, employee_name, department_id)
SELECT employee_id, employee_name, department_id
FROM employees;
Correlated Subqueries
Correlated subqueries in INSERT statements involve a subquery that refers to columns in the outer query. This type of subquery is evaluated once for each row processed by the outer query.
Example: Correlated Subquery in INSERT
Suppose you have an employees table and a departments table. You want to insert employees into a sales_team table only if they belong to the ‘Sales’ department.
Table: departments
Table: new_employees
| department_id | department_name |
| 10 | Sales |
| 20 | Marketing |
Table: sales_team
| employee_id | employee_name | department_id |
Goal: Insert employees from the employees table into the sales_team table if they are in the ‘Sales’ department.
INSERT INTO sales_team (employee_id, employee_name, department_id)
SELECT employee_id, employee_name, department_id
FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Sales'
);
Subqueries with Joins
Subqueries can be combined with joins to insert data from multiple tables into the target table.
Example: Subquery with Joins in INSERT
Suppose you have employees, salaries, and employee_salaries tables. You want to insert employee IDs, names, and their salaries into the employee_salaries table.
Table: salaries
| employee_id | salary |
| 1 | 50000 |
| 2 | 60000 |
| 3 | 55000 |
Table: employee_salaries
| employee_id | employee_name | salary |
Goal: Insert data into employee_salaries from employees and salaries.
INSERT INTO employee_salaries (employee_id, employee_name, salary)
SELECT e.employee_id, e.employee_name, s.salary
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id;
Advanced Use Cases
1. Inserting Aggregated Data
Subqueries can be used to insert aggregated data into a target table.
Example: Insert the total salary per department into a department_salaries table.
Table: department_salaries
| department_id | total_salary |
Goal: Insert total salary data from the salaries and employees tables into the department_salaries table.
INSERT INTO department_salaries (department_id, total_salary)
SELECT e.department_id, SUM(s.salary)
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id
GROUP BY e.department_id;
2. Inserting Conditional Data
Subqueries can conditionally insert data based on specific criteria.
Example: Insert employees into high_earners table if their salary exceeds a certain threshold.
Table: high_earners
| employee_id | employee_name | salary |
Goal: Insert employees earning more than 55000.
INSERT INTO high_earners (employee_id, employee_name, salary)
SELECT e.employee_id, e.employee_name, s.salary
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id
WHERE s.salary > 55000;
Performance Considerations
When using subqueries in INSERT statements, consider the following performance tips:
Optimize Subqueries: Ensure subqueries are optimized for performance, especially when dealing with large datasets.
Indexing: Proper indexing on columns used in subqueries can significantly improve performance.
Avoid Unnecessary Complexity: Keep subqueries as simple as possible to avoid unnecessary computational overhead.
Practical Applications
1. Data Migration
Subqueries in INSERT statements can be used for data migration, moving data from old tables to new tables with different schemas.
Example: Migrate data from old_employees to new_employees with additional processing.
Table: old_employees
| id | name | dept |
| 1 | Alice | 10 |
| 2 | Bob | 20 |
Table: new_employees
| employee_id | employee_name | department_id |
INSERT INTO new_employees (employee_id, employee_name, department_id)
SELECT id, name, dept
FROM old_employees;
2. Data Transformation
Subqueries can transform data during the insertion process, such as calculating derived values.
Example: Insert employees into a salary_grade table based on their salary range.
Table: salary_grade
| employee_id | employee_name | grade |
INSERT INTO salary_grade (employee_id, employee_name, grade)
SELECT e.employee_id, e.employee_name,
CASE
WHEN s.salary > 70000 THEN 'A'
WHEN s.salary BETWEEN 50000 AND 70000 THEN 'B'
ELSE 'C'
END AS grade
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id;
Conclusion
Using subqueries in INSERT statements is a powerful technique in SQL that allows for dynamic and complex data manipulations. By understanding the different types of subqueries—simple, correlated, and with joins—you can effectively populate tables with data derived from various sources and criteria. Whether you are aggregating data, performing conditional inserts, or transforming data during migration, subqueries provide a robust solution for advanced database operations. The examples and explanations provided in this article should serve as a comprehensive guide to mastering subqueries in INSERT statements for your SQL projects.