SQL Subqueries

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

“SQL Subqueries”의 55개의 댓글

  1. 핑백: SSCN BKN 2021 Kabupaten Mukomuko

  2. 핑백: mơ thấy cóc đánh con gì

  3. 핑백: cây trồng trong phòng làm việc

  4. 핑백: mơ thấy mít chín đánh con gì

  5. 핑백: RPA in software testing

  6. 핑백: tai game dua ngua

  7. 핑백: nằm mơ thấy đứt tay chảy máu

  8. 핑백: mơ thấy bàn thờ

  9. 핑백: chup anh thay ma

  10. 핑백: Tyson vs Jones jr live stream online

  11. 핑백: cbd oil buy canada

  12. 핑백: Azure Devops

  13. 핑백: 안전공원

  14. 핑백: incredit patēriņa kredīts

  15. 핑백: www.manausa.com

  16. 핑백: nằm mơ thấy phật bà quan âm

  17. 핑백: mơ thấy đi chợ

  18. 핑백: mơ thấy chìa khóa

  19. 핑백: mơ cá sấu đánh con gì

  20. 핑백: nằm mơ thấy bướm

  21. 핑백: michigan state cornhole

  22. 핑백: mơ thấy chim bồ câu đánh con gì

  23. 핑백: mơ thấy chiến tranh

  24. 핑백: site

  25. 핑백: Maytag UXT5230AYS manuals

  26. 핑백: Arcam Audio tuners manuals

  27. 핑백: card dumps

  28. 핑백: คอนโดปูนา

  29. 핑백: Paket Wisata Bali

  30. 핑백: Industrielle Dampfkessel für ganz Deutschland

  31. 핑백: sun city west retirement community homes for sale

  32. 핑백: Ritalin till salu

  33. 핑백: crabs for sale

  34. 핑백: relx pod

  35. 핑백: health insurance

  36. 핑백: sex offenders in my area montreal

  37. 핑백: Toys for Girls

  38. 핑백: ks pod

  39. 핑백: ดูบอลสด

  40. I would like to thank you for the efforts you’ve put in penning this blog. I really hope to see the same high-grade blog posts by you later on as well. In fact, your creative writing abilities has inspired me to get my own, personal website now ;)|

답글 남기기