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