Subquery is a nested query in another SQL query. Another SQL query is known as main query and nested query is known as Subquery. Subquery always in the parenthesis. Subquery executes only once before executing the main query. Subquery output used by the main query. Subquery used in SELECT, INSERT UPDATE, DELETE statements.

The rules to be followed while using subquery are listed as follows –

• There is only one single column in SELECT statement in subquery.
• We cannot use ORDER BY clause in subquery, but we can use ORDER BY clause in main query.
• We cannot use BETWEEN operator with subquery, but we can use BETWEEN operator in main query.
• Subquery returns multiple rows when we used IN operator in main query.
• We can use comparison operators like <, >, = in subquery.

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Getting rows from table by using subquery.

Requirement – Getting rows of specific columns from employee_details whose dept_name is office in department_details table by using subquery. The query was as follows –

SELECT emp_id,emp_name FROM employee_details 
WHERE dept_id IN 
(SELECT dept_id FROM department_details WHERE dept_name = 'Office'); 

By executing above query, we can get emp_id, emp_name details from employee_details where dept_name is Office in department_details. The output was as follows –

Scenario – Inserting rows in existing table by using subquery.

Requirement – Inserting rows in existing table employee_info from employee_details table by using subquery. The query was as follows –

INSERT INTO employee_info 
SELECT * FROM employee_details 
WHERE emp_id IN 
(SELECT emp_id FROM employee_details  WHERE dept_id = 2000); 

By executing above query, we can INSERT employee_details table rows in employee_info whose dept_id is 1000. The output was as follows -

Scenario – Updating rows in existing table by using subquery.

Requirement – Updating rows in existing employee_details table by using subquery. The query was as follows –

UPDATE employee_details SET salary = 30000 
WHERE dept_id IN 
(SELECT dept_id FROM department_details WHERE dept_name = 'Office'); 

By executing above query, we can update salary details from employee_details where dept_name is Office in department_details. The output was as follows –

Scenario – Deleting rows in existing table by using subquery.

Requirement – Deleting rows in existing employee_details table by using subquery. The query was as follows –

DELETE FROM employee_details 
WHERE dept_id IN 
(SELECT dept_id FROM department_details WHERE dept_name = 'Sales'); 

By executing above query, we can update salary details from employee_details where dept_name is Sales in department_details. The output was as follows –