SQL Subquery

A Subquery is a 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 brackets ().

Example

#

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.

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

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.

A 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 to each record.
SELECT column1 = (SELECT column-name 
                    FROM table-name 
                   WHERE condition),
       column-names
  FROM table-name
 WHERE condition

More Examples

Subquery with IN

ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products with order quantities greater than 100.
SELECT ProductName
  FROM Product
 WHERE Id IN (SELECT ProductId
                FROM OrderItem
               WHERE Quantity > 100)
Result: 12 records.
ProductName
Guaraná Fantástica
Schoggi Schokolade
Chartreuse verte
Jack's New England Clam Chowder
Rogede sild
Manjimup Dried Apples
Perth Pasties

Subquery assigning column value

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
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 called a correlated subquery because the subquery references the enclosing query, specifically, the C.Id in the WHERE clause.

Result:  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

You may also like

Guides


vsn 3.1