SQL Subqueries

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

1. SELECT column–names
2. FROM table–name1
3. WHERE value IN (SELECT column–name
4. FROM table–name2
5. WHERE condition)

Subqueries can also assign column values for each record:

1. SELECT column1 = (SELECT column–name FROM table–name WHERE condition),
2. column–names
3. FROM table–name
4. WEHRE condition

 

---

 

SQL Subquery Examples

Problem: List products with order quantities greater than 100.

1. SELECT ProductName
2. FROM Product
3. WHERE Id IN (SELECT ProductId
4. FROM OrderItem
5. 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

1. SELECT FirstName, LastName,
2. OrderCount = (SELECT COUNT(O.Id) FROM [Order] O WHERE O.CustomerId = C.Id)
3. 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