SQL Subquery

List all suppliers with the number of products they offer.

SELECT CompanyName, 
       ProductCount = (SELECT COUNT(P.id)
                         FROM [Product] P
                        WHERE P.SupplierId = S.Id)
  FROM Supplier S

The nested SELECT between brackets is the Subquery.

CompanyName	ProductCount
Exotic Liquids	3
New Orleans Cajun Delights	4
Grandma Kelly's Homestead	3
Tokyo Traders	3
Cooperativa de Quesos 'Las Cabras'	2

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.

A subquery with the IN operator.

SELECT column-names
  FROM table-name1
 WHERE value IN (SELECT column-name
                   FROM table-name2
                  WHERE condition)

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

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

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

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

This is called a correlated subquery because the subquery references the enclosing query, specifically, the C.Id in the WHERE clause.

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

• Our Sql SELECT Tutorial
• Our Sql JOIN Tutorial
• Our Sql WHERE Tutorial
• Our Sql Aggregate Functions Reference