SQL Subqueries

What is subquery in SQL?

A subquery is a SQL query within a query.
They are nested queries that provide data to the enclosing query.

Subqueries can return individual values or a list of records.
Note that subquery statements are enclosed between parenthesis.

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 a 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

ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued

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)

Result: 12 records.

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

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

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

Result: 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