When using a subquery in the SELECT statement, we can apply it in these clauses: SELECT, FROM, WHERE, and HAVING. In this article, we will see examples of using subqueries in these four places.
All examples provided in this article use Employee and Department tables described in Table 1 and Table 2 below.
Table 1. Employee table
Table 2. Department table
Let’s start with the WHERE clause because we mostly find subqueries added within this clause.
Subquery in WHERE clause
An SQL subquery in the WHERE clause is useful when we want to make a comparison between the result of the inner query and the outer one.
The following query finds the employees whose department is located in Denver.
SELECT EmpCode, EmpFname, EmpLname, EmpSalary, DeptCode FROM Employee WHERE DeptCode = ( SELECT DeptCode FROM Department WHERE DeptLocation = 'Denver' )
Table 3. Result of Example 1
The inner query returns D-04, which is the code of department with location equals to Denver. Then, this value is used by the outer query as a comparison. The final result set has two rows, containing only employees with department code equals to D-04.
Now, let’s look at another example. The following query gets the employee with the highest salary. Notice that the comparison in this example uses a single table.
SELECT EmpCode, EmpFname, EmpLname, EmpSalary, DeptCode FROM Employee WHERE EmpSalary = ( SELECT MAX(EmpSalary) FROM Employee )
Table 4. Result of Example 2
The inner query returns the maximum salary, which is 150000. Then, the outer query evaluates the employee record that has a salary equal to this value. Finally, it returns one row as a result.
Subquery in SELECT clause
This type of subquery is also called scalar subquery because the inner query returns a single value.
The following query gets the employee names, department codes, and department locations. Notice that the locations are retrieved using a subquery in the SELECT clause.
SELECT e.EmpFname , e.EmpLname , e.DeptCode , (SELECT DeptLocation FROM Department d WHERE d.DeptCode = e.DeptCode) AS DeptLocation FROM Employee e
Table 5. Result of Example 3
For every row that is returned by the outer query, the inner query gets the DeptLocation value from the Department table, which has the code equals to the DeptCode column from the outer query.
The following query gets the list of departments and the average employee salary by each department.
SELECT d.DeptCode , d.DeptLocation , (SELECT AVG(e.EmpSalary) FROM Employee e WHERE e.DeptCode = d.DeptCode) AS AvgSalary FROM Department d
Table 6. Result of Example 4
From the results above, we can see that the AvgSalary for the department with code D-01 is NULL. That is because there are no employees in the department located in Chicago.
Subquery in FROM clause
Subqueries that are used in the FROM clauses act as a virtual table. The inner query returns result set that we can call the derived table, where we can select the columns and perform aggregate functions on it.
This type of subquery is also called inline view. See What is Inline View in SQL?
Assume that employees with salary > 100000 are managers, and then want to find the average salary of these managers by department code.
We can do this by creating a subquery that its inner query returns the department code and the salary of the managers only. Then, in the outer query, we calculate the average of the salary by department code.
SELECT e.DeptCode, AVG(e.EmpSalary) AvgManagerSalary FROM ( SELECT DeptCode, EmpSalary FROM Employee WHERE EmpSalary > 100000 ) e GROUP BY e.DeptCode
Table 7. Result of Example 5
Let’s pay attention to the AvgManagerSalary value for the department with code D-02. There are actually two employees in this department (see Table 1). However, the employee with the name Anthony Harris is not returned by the inner query because his salary is under 100000. Thus, the outer query only calculates the record of Darin Wilson and returns his salary as the final AvgManagerSalary for D-02.
Subquery in HAVING clause
We can use subqueries in the HAVING clause to filter aggregate values, such as sum, count, average, maximum, and minimum.
The following query gets the list of department codes and the average salary of employees by the departments that are greater than the average salary of employees in the department D-04.
SELECT DeptCode, AVG(EmpSalary) AS AvgSalary FROM Employee GROUP BY DeptCode HAVING AVG(EmpSalary) > ( SELECT AVG(EmpSalary) FROM Employee WHERE DeptCode = 'D-04' )
Table 8. Result of Example 6
The inner query returns 115000, which is the average salary of the department with code D-04. This value is then used to filter the average values in the outer query.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.