A Subquery is a SQL query within a query.
Subqueries provide data to the enclosing query.
Subqueries can return individual values or a list of records.
Subqueries must be enclosed with parenthesis.
Problem: List all suppliers and the number of products they offer.
copySELECT CompanyName,
ProductCount = (SELECT COUNT(P.id)
FROM [Product] P
WHERE P.SupplierId = S.Id)
FROM Supplier S
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
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
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 |
 |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
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 |
| |
||
