A subquery, also known as a nested query or inner query, is a query that is embedded within another query. A subquery is used to restrict or enhance the data that is used by the main query. It does this by providing additional filtering criteria or by providing additional data for the main query to use. By using a subquery, you can refine the results of the main query to only include the data that you are interested in. This can be useful for creating more complex and powerful queries that can provide more accurate and useful results.
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
Retrieve the employees whose salaries are above the average salary among all employees
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 categories of a subquery: Correlated and Non-Correlated.
Correlated subqueries
A correlated subquery is a type of subquery that is executed multiple times, once for each row of the outer query. Unlike a regular subquery, which is executed once and then returns a result that is used by the outer query, a correlated subquery obtains its values from the outer query each time it is executed. This allows the subquery to be tailored to the specific row of the outer query that it is being executed for, and to return results that are specific to that row. When the subquery returns, it passes its results back to the outer query, which can then use them to further filter or process the data. This allows you to create more complex and powerful queries that can provide more detailed and accurate results.
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 is a type of subquery that runs separately from the main query and provides the main query with its results. The subquery is executed first, and then the main query uses those results to continue processing.
Example
For 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 |
Code Runner
Copy the below queries in the code runner to see the result
SELECT *
FROM Employee e
WHERE salary > (select avg(salary) from Employee)
ORDER BY e.salary;
SELECT *
FROM Employee e
WHERE salary > (SELECT avg(salary) FROM Employee e2
WHERE e2.dept_id=e.dept_id)
ORDER BY emp_name, salary;
SELECT * FROM Employee
WHERE dept_id NOT IN (SELECT dept_id FROM Department);