SQL Subqueries

  • A subquery is a SQL query within a query.
  • Subqueries are nested queries that provide data to the enclosing query.
  • Subqueries can return individual values or a list of records
  • Subqueries must be enclosed with parenthesis
The definitive guide
for data professionals


Order today!
 See 2 min video



The SQL subquery syntax


There is no general syntax; subqueries are regular queries placed inside parenthesis.
Subqueries can be used in different ways and at different locations inside a query:
Here is an subquery with the IN operator
SELECT column-names
  FROM table-name1
 WHERE value IN (SELECT column-name
                   FROM table-name2 
                  WHERE condition)

Subqueries can also assign column values for each record:

SELECT column1 = (SELECT column-name FROM table-name WHERE condition),
       column-names
  FROM table-name
 WEHRE condition




SQL Subquery Examples





Problem: List products with order quantities greater than 100.

SELECT ProductName
  FROM Product 
 WHERE Id IN (SELECT ProductId 
                FROM OrderItem
               WHERE Quantity > 100)


Results: 12 records

PoductName
Guaraná Fantástica
Schoggi Schokolade
Chartreuse verte
Jack's New England Clam Chowder
Rogede sild
Manjimup Dried Apples
Perth Pasties




SQL Subquery Examples



Problem: List all customers with their total number of orders

SELECT FirstName, LastName, 
       OrderCount = (SELECT COUNT(O.Id) FROM [Order] O WHERE O.CustomerId = C.Id)
  FROM Customer C 

This is a correlated subquery because the subquery references the enclosing query (i.e. the C.Id in the WHERE clause).


Results: 91 records

FirstName LastName OrderCount
Maria Anders 6
Ana Trujillo 4
Antonio Moreno 7
Thomas Hardy 13
Christina Berglund 18
Hanna Moos 7
Frédérique Citeaux 11
Martín Sommer 3