Summary -

In this topic, we described about the Subquery with detailed example.

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.

Example –

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

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee6 Analyst Employee5 2019-07-11 27000.00 1000
011 Employee8 Salesman Employee5 2019-09-09 10000.00 1000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

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 –

emp_id emp_name
001 Employee1
003 Employee3
005 Employee5
006 Employee6
007 Employee7
010 Employee6
011 Employee8

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 -

emp_id emp_name designation manager_id date_of_hire salary dept_id
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Note :- Make sure that employee_info table have same structure of employee_details table while inserting rows.

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 –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 30000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 30000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 30000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 30000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 30000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee6 Analyst Employee5 2019-07-11 30000.00 1000
011 Employee8 Salesman Employee5 2019-09-09 30000.00 1000

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 –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 30000.00 1000
003 Employee3 Manager Employee1 2019-07-11 30000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 30000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 30000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 30000.00 1000
010 Employee6 Analyst Employee5 2019-07-11 30000.00 1000
011 Employee8 Salesman Employee5 2019-09-09 30000.00 1000