SQL inserting records using nested subqueries with any operator
In this page, we are going to discuss, how two or more subqueries can be implemented in an INSERT INTO statement to insert rows into a table.
Example:
Sample table: agent1
Sample table: agents
Sample table: customer
Sample table: orders
To insert records into 'agent1' table from 'agents' table with following conditions -
1. 'agent_code' of agents table must be any 'agent_code' from 'customer' table which satisfies the condition bellow :
2. 'agent_code' of customer table must be any 'agent_code' from 'orders' table which satisfies the condition bellow :
3. 'advance_amount' of 'orders' table must be more than 600,
the following SQL statement can be used:
SQL Code:
-- This SQL code attempts to insert selected rows from the 'agents' table into the 'agent1' table, filtered by nested subqueries.
-- INSERT INTO statement begins
INSERT INTO agent1
-- Specifies the target table 'agent1' where the data will be inserted
SELECT * FROM agents
-- Selects all columns and rows from the 'agents' table
WHERE agent_code=ANY(
-- Specifies a condition involving a subquery to filter the rows from the 'agents' table
SELECT agent_code FROM customer
-- Selects the 'agent_code' column from the 'customer' table in the subquery
WHERE agent_code =ANY(
-- Specifies another condition involving a nested subquery to further filter the rows
SELECT agent_code FROM orders
-- Selects the 'agent_code' column from the 'orders' table in the nested subquery
WHERE advance_amount>600));
-- Filters the rows selected from the 'orders' table based on the condition that the 'advance_amount' is greater than 600
-- The intermediate subquery selects the 'agent_code' column from the 'customer' table, filtering the rows based on the condition that the 'agent_code' must match any agent code retrieved from the nested subquery result
-- The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from the intermediate subquery result
Explanation:
- This SQL code aims to copy selected rows from the 'agents' table to the 'agent1' table, based on nested subqueries involving conditions from the 'customer' and 'orders' tables.
- The INSERT INTO statement specifies the target table 'agent1' where the data will be inserted.
- The SELECT statement retrieves all columns and rows from the 'agents' table.
- The WHERE clause includes a condition involving nested subqueries. The innermost subquery selects the 'agent_code' column from the 'orders' table, filtering the rows based on the condition that the 'advance_amount' must be greater than 600.
- The intermediate subquery selects the 'agent_code' column from the 'customer' table, filtering the rows based on the condition that the 'agent_code' must match any agent code retrieved from the nested subquery result.
- The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from the intermediate subquery result.
- As a result, only the rows from the 'agents' table corresponding to agents associated with customers having orders with an advance amount greater than 600 will be inserted into the 'agent1' table.
SQL insert using nested subqueries with any operator and group by
In the following we are going to discuss, how an ANY operator with GROUP BY can be used in an INSERT INTO statement to insert records into a table.
Example:
Sample table: agent1
Sample table: agents
Sample table: customer
Sample table: orders
To insert records into 'agent1' table from 'agents' table with following conditions -
1. 'agent_code' of agents table must be any 'agent_code' from 'customer' table which satisfies the condition bellow :
2. 'agent_code' of customer table must be any 'agent_code' from 'orders' table which satisfies the condition bellow :
3. 'ord_amount' of 'orders' table must be more than 1000,
4. 'ord_amount' of 'orders' table should arrange in a group,
the following SQL statement can be used:
SQL Code:
-- This SQL code attempts to insert selected rows from the 'agents' table into the 'agent1' table, filtered by nested subqueries.
-- INSERT INTO statement begins
INSERT INTO agent1
-- Specifies the target table 'agent1' where the data will be inserted
SELECT * FROM agents
-- Selects all columns and rows from the 'agents' table
WHERE agent_code=ANY(
-- Specifies a condition involving a subquery to filter the rows from the 'agents' table
SELECT agent_code FROM customer
-- Selects the 'agent_code' column from the 'customer' table in the subquery
WHERE agent_code =ANY(
-- Specifies another condition involving a nested subquery to further filter the rows
SELECT agent_code FROM orders
-- Selects the 'agent_code' column from the 'orders' table in the nested subquery
WHERE ord_amount>1000
-- Filters the rows selected from the 'orders' table based on the condition that the 'ord_amount' is greater than 1000
GROUP BY ord_amount));
-- Groups the rows selected from the 'orders' table by 'ord_amount' and retrieves the unique 'agent_code' values
-- The intermediate subquery selects the 'agent_code' column from the 'customer' table, filtering the rows based on the condition that the 'agent_code' must match any agent code retrieved from the nested subquery result
-- The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from the intermediate subquery result
Explanation:
- This SQL code aims to copy selected rows from the 'agents' table to the 'agent1' table, based on nested subqueries involving conditions from the 'customer' and 'orders' tables.
- The INSERT INTO statement specifies the target table 'agent1' where the data will be inserted.
- The SELECT statement retrieves all columns and rows from the 'agents' table.
- The WHERE clause includes a condition involving nested subqueries. The innermost subquery selects the 'agent_code' column from the 'orders' table, filtering the rows based on the condition that the 'ord_amount' must be greater than 1000.
- The intermediate subquery selects the 'agent_code' column from the 'customer' table, filtering the rows based on the condition that the 'agent_code' must match any agent code retrieved from the nested subquery result.
- The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from the intermediate subquery result.
- As a result, only the rows from the 'agents' table corresponding to agents associated with customers having orders with an order amount greater than 1000 will be inserted into the 'agent1' table.
SQL insert using nested subqueries with any operator and group by and order by
In the following we are going to discuss, how an ANY operator with ORDER BY and GROUP BY can be used in an INSERT INTO statement to insert records into a table.
Example:
Sample table: agent1
Sample table: agents
Sample table: customer
Sample table: orders
To insert records into 'agent1' table from 'agents' table with following conditions -
1. 'agent_code' of agents table must be any 'agent_code' from 'customer' table which satisfies the condition bellow :
2. 'agent_code' of customer table must be any 'agent_code' from 'orders' table which satisfies the condition bellow :
3. 'advance_amount' of 'orders' table must be more than 600,
4. same 'advance_amount' of 'orders' table should come in a group,
5. 'advance_amount' of 'orders' table should be arranged in ascending order,
the following SQL statement can be used:
SQL Code:
-- This SQL code attempts to insert selected rows from the 'agents' table into the 'agent1' table, filtered by nested subqueries.
-- INSERT INTO statement begins
INSERT INTO agent1
-- Specifies the target table 'agent1' where the data will be inserted
SELECT * FROM agents
-- Selects all columns and rows from the 'agents' table
WHERE agent_code=ANY(
-- Specifies a condition involving a subquery to filter the rows from the 'agents' table
SELECT agent_code FROM customer
-- Selects the 'agent_code' column from the 'customer' table in the subquery
WHERE agent_code =ANY(
-- Specifies another condition involving a nested subquery to further filter the rows
SELECT agent_code FROM orders
-- Selects the 'agent_code' column from the 'orders' table in the nested subquery
WHERE advance_amount>600
-- Filters the rows selected from the 'orders' table based on the condition that the 'advance_amount' is greater than 600
GROUP BY advance_amount
-- Groups the rows selected from the 'orders' table by 'advance_amount'
ORDER BY advance_amount));
-- Orders the rows selected from the 'orders' table in ascending order based on the 'advance_amount'
-- The intermediate subquery selects the 'agent_code' column from the 'customer' table, filtering the rows based on the condition that the 'agent_code' must match any agent code retrieved from the nested subquery result
-- The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from the intermediate subquery result
Explanation:
- This SQL code aims to copy selected rows from the 'agents' table to the 'agent1' table, based on nested subqueries involving conditions from the 'customer' and 'orders' tables.
- The INSERT INTO statement specifies the target table 'agent1' where the data will be inserted.
- The SELECT statement retrieves all columns and rows from the 'agents' table.
- The WHERE clause includes a condition involving nested subqueries. The innermost subquery selects the 'agent_code' column from the 'orders' table, filtering the rows based on the condition that the 'advance_amount' must be greater than 600.
- The intermediate subquery selects the 'agent_code' column from the 'customer' table, filtering the rows based on the condition that the 'agent_code' must match any agent code retrieved from the nested subquery result.
- The outer query filters the rows selected from the 'agents' table based on the condition that the 'agent_code' must be equal to any agent code retrieved from the intermediate subquery result.
- The rows selected from the 'orders' table are also grouped by 'advance_amount' and ordered in ascending order based on 'advance_amount'.
SQL insert using subqueries with max() function
In the following we are going to discuss, how an MAX() function in a subquery can be used in an INSERT INTO statement to insert records into a table.
Example:
Sample table: highorder
Sample table: orders
To add values against 'ord_amount','ord_date','cust_code' columns in 'highorder' table from 'orders' table with following conditions -
1. 'orders' table have defined as alias 'a' and alias 'b',
2. 'ord_amount' of alias 'a' must equal to maximum 'ord_amount' of alias 'b' which satisfies the condition bellow :
3. 'ord_date' of alias 'a' and alias 'b' must be same,
the following SQL statement can be used:
SQL Code:
-- This SQL code attempts to insert selected rows into the 'highorder' table based on a subquery.
-- INSERT INTO statement begins
INSERT INTO highorder
-- Specifies the target table 'highorder' where the data will be inserted
SELECT ord_amount,ord_date,cust_code
-- Selects specific columns from the 'orders' table
FROM orders a
-- Alias 'a' is assigned to the 'orders' table
WHERE ord_amount=
-- Specifies a condition to filter rows from the 'orders' table
(SELECT MAX(ord_amount)
-- Selects the maximum 'ord_amount' from the 'orders' table
FROM orders b
-- Alias 'b' is assigned to the 'orders' table (used in the subquery)
WHERE a.ord_date=b.ord_date);
-- Filters rows from the 'orders' table where the 'ord_amount' matches the maximum 'ord_amount' for each 'ord_date'
Explanation:
- This SQL code inserts selected rows into the 'highorder' table from the 'orders' table based on a subquery.
- The INSERT INTO statement specifies the target table 'highorder' where the data will be inserted.
- The SELECT statement selects specific columns ('ord_amount', 'ord_date', and 'cust_code') from the 'orders' table.
- The alias 'a' is assigned to the 'orders' table in the outer query to differentiate it from the 'orders' table in the subquery.
- The WHERE clause includes a condition that filters rows from the 'orders' table. It selects rows where the 'ord_amount' is equal to the maximum 'ord_amount' for each 'ord_date'.
- The subquery within the WHERE clause selects the maximum 'ord_amount' for each 'ord_date' from the 'orders' table. It uses alias 'b' for the 'orders' table to distinguish it from the outer query.
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Inserting the result of a query in another table
Next: Update statement
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics