SQL Subquery Tutorial


A SQL subquery is a query within another query.  Subqueries make it possible for you to write queries that are more dynamic, and data driven. 

In this tutorial we will show you how to write a sql subquery as part of the SELECT statement.  What can make subqueries confusing is they can be used wherever an expression is expected. 

This means we can use subqueries as columns, and in the following clauses:

Subqueries return two types of results:  either a single value called a scalar value, or a table value, which is akin to a query result. 

SQL Subquery Example 

Let’s look at how to write a simple subquery for the select statement. 

Here is the general form we’re going to use: 

SELECT column1, column2, (subquery1) 
FROM   table1 

Here we will select every employee and also return the average age for all employees.  We will use a subquery to calculate the average vacation hours. 

Try it for yourself! 

SELECT NationalIDNumber, JobTitle, VacationHours, (SELECT AVG(VacationHours) FROM HumanResources.Employee) AvgVacationHours FROM HumanResources.Employee
SELECT NationalIDNumber,  
       JobTitle, 
       VacationHours,  
       (SELECT AVG(VacationHours) 
	    FROM HumanResources.Employee) AvgVacationHours 
 FROM HumanResources.Employee

Notice that the subquery is surrounded by parenthesis () and aliased as AvgVacationHours. 

When using subqueries in the column list, make sure your query returns a single result; otherwise your query returns an error.  This makes sense as a column have a single value. 

SQL Subquery in WHERE 

Let’s see how to write a subquery in the where clause.  To do this we will use IN operator to test whether a value is part of the result the subquery returns. 

Here is the general format: 

SELECT column1, column2,.. 
FROM table1 
WHERE column2 IN (subquery1) 

An example makes this clearer.  Suppose we want to find all sales orders made by a salesperson having a bonus greater than 5000 dollars. 

To do this we’ll write two queries.  The outer to find the sales orders, and an inner (the SQL subquery) to return SalePersonID for $5000 and above bonus earners. 

I color coded the subquery so you can easily see it: 

SELECT SalesOrderID
               ,OrderDate
               ,AccountNumber
               ,CustomerID
               ,SalesPersonID
               ,TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IN (SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE Bonus > 5000
)

When run as a subquery, it returns a list of ID’s.  A outer query row is returned if its SalesPersonID is within this list.  Here is the example you can try:

SELECT SalesOrderID
,OrderDate
,AccountNumber
,CustomerID
,SalesPersonID
,TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IN (SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE Bonus > 5000
)

Important Points: 

When working with SQL subqueries keep these points in mind: 

  1. A subquery is enclosed in parenthesis ()
  1. Alias subqueries in your column list to make it easier to read and reference the results. 
  1. A subquery either returns a single value or table.  Be aware of which type your query returns to ensure it works correctly in the situation. 
  1. Subqueries returning tables, return errors when located within the select list. 

Additional SQL Subquery Resources 

Subqueries are used throughout SQL.  To learn more about subqueries and their uses, check out these articles: 

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>