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

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

---