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 columnnames
  2. FROM tablename1
  3. WHERE value IN (SELECT columnname
  4. FROM tablename2
  5. WHERE condition)

Subqueries can also assign column values for each record:

  1. SELECT column1 = (SELECT columnname FROM tablename WHERE condition),
  2. columnnames
  3. FROM tablename
  4. WEHRE condition

 


 

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

 


 

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
댓글 남기기