Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

4 Places We Can Use Subqueries in SELECT Statements – Querychat

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.

EmpCode EmpFname EmpLname EmpSalary DeptCode
E-01 Michelle Marks 150000 D-03
E-02 Donald Collins 120000 D-04
E-03 Anthony Harris 80000 D-02
E-04 Darin Wilson 130000 D-02
E-05 Susan Douglas 110000 D-04
E-06 Katherine Johnson 100000 D-03

Table 1. Employee table

DeptCode DeptLocation
D-01 Chicago
D-02 Dallas
D-03 Seattle
D-04 Denver

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.

Example 1:

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'
)

Result:

EmpCode EmpFname EmpLname EmpSalary DeptCode
E-02 Donald Collins 120000 D-04
E-05 Susan Douglas 110000 D-04

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.

Example 2:

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
)

Result:

EmpCode EmpFname EmpLname EmpSalary DeptCode
E-01 Michelle Marks 150000 D-03

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.

Example 3:

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

Result:

EmpFname EmpLname DeptCode DeptLocation
Michelle Marks D-03 Seattle
Donald Collins D-04 Denver
Anthony Harris D-02 Dallas
Darin Wilson D-02 Dallas
Susan Douglas D-04 Denver
Katherine Johnson D-03 Seattle

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.

Example 4: 

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

Result:

DeptCode DeptLocation AvgSalary
D-01 Chicago NULL
D-02 Dallas 105000
D-03 Seattle 125000
D-04 Denver 115000

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? 

Example 5:

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

Result:

DeptCode AvgManagerSalary
D-02 130000
D-03 150000
D-04 115000

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.

Example 6:

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'
  )

Result:

DeptCode AvgManagerSalary
D-03 150000

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc