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),
  FROM table-name
 WEHRE condition

SQL Subquery Examples

Problem: List products with order quantities greater than 100.

SELECT ProductName
  FROM Product 
                FROM OrderItem
               WHERE Quantity > 100)

Results: 12 records

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