SQL Subqueries ~ niit post

• A subquery is a SQL query within a query.
• Subqueries are nested queries that provide data to the enclosing query.
• Subqueries can return individual values or a list of records
• Subqueries must be enclosed with parenthesis

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 

1.  SELECT column-names

2.    FROM table-name1

3.   WHERE value IN (SELECT column-name

4.                     FROM table-name2 

5.                    WHERE condition)

Subqueries can also assign column values for each record: 

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

2.         column-names

3.    FROM table-name

4.   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. 

1.  SELECT ProductName

2.    FROM Product 

3.   WHERE Id IN (SELECT ProductId 

4.                  FROM OrderItem

5.                 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 

1.  SELECT FirstName, LastName, 

2.   OrderCount = (SELECT COUNT(O.Id) FROM [Order] O WHERE O.CustomerId = C.Id)

3.    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
...