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
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,..
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:
WHERE SalesPersonID IN (SELECT BusinessEntityID
WHERE Bonus > 5000
When working with SQL subqueries keep these points in mind:
- A subquery is enclosed in parenthesis ().
- Alias subqueries in your column list to make it easier to read and reference the results.
- 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.
- 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:
- Introduction to Subqueries
- Subqueries in SELECT Statement
- Subqueries in WHERE Clause
- Subquery in the FROM Clause