What is a Subquery? What are its types?

A subquery is a query within another query, also known as a nested query or inner query. It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively.

Employee Table

emp_id emp_name salary dept_id manager_id
E1 Rahul 15000 D1 M1
E2 Manoj 15000 D1 M1
E3 James 55000 D2 M2
E4 Michael 25000 D2 M2
E5 Ali 20000 D10 M3
E6 Robin 35000 D10 M3

Department Table

dept_id dept_name
D1 IT
D2 HR
D3 Finance
D4 Admin

Example

Here, for example, we find the employees whose salary is more than the average salary earned by all employees and filter them based on the avg salary.

SELECT *
FROM Employee e
WHERE salary > (select avg(salary) from Employee)
ORDER BY e.salary;

Output

emp_id emp_name salary dept_id manager_id
E6 Robin 35000 D10 M3
E3 James 55000 D2 M2

There are two types of subquery - Correlated and Non-Correlated.

Correlated subqueries

A correlated subquery typically obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query.

Example

Here, we find the employees in each department who earn more than the average salary in that department and filter data from employee tables based on avg salary from the result.

SELECT  *
FROM Employee e
WHERE salary > (SELECT avg(salary) FROM Employee e2 
WHERE e2.dept_id=e.dept_id)
ORDER BY emp_name, salary;

Output

emp_id emp_name salary dept_id manager_id
E3 James 55000 D2 M2
E6 Robin 35000 D10 M3

Non-correlated subqueries

A noncorrelated subquery executes independently of the outer query. The subquery executes first and then passes its results to the outer query.

Example

In an example, we find employees who do not have any department.

SELECT *  FROM Employee 
WHERE dept_id  NOT IN (SELECT dept_id FROM Department);

Output

emp_id emp_name salary dept_id manager_id
E5 Ali 20000 D10 M3
E6 Robin 35000 D10 M3