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

Comments (70)

  1. Pingback: SSCN BKN 2021 Kabupaten Mukomuko

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

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

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

  5. Pingback: RPA in software testing

  6. Pingback: tai game dua ngua

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

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

  9. Pingback: chup anh thay ma

  10. Pingback: Tyson vs Jones jr live stream online

  11. Pingback: cbd oil buy canada

  12. Pingback: Azure Devops

  13. Pingback: 안전공원

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

  15. Pingback: www.manausa.com

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

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

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

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

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

  21. Pingback: michigan state cornhole

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

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

  24. Pingback: site

  25. Pingback: Maytag UXT5230AYS manuals

  26. Pingback: Arcam Audio tuners manuals

  27. Pingback: card dumps

  28. Pingback: คอนโดปูนา

  29. Pingback: Paket Wisata Bali

  30. Pingback: Industrielle Dampfkessel für ganz Deutschland

  31. Pingback: sun city west retirement community homes for sale

  32. Pingback: Ritalin till salu

  33. Pingback: crabs for sale

  34. Pingback: relx pod

  35. Pingback: health insurance

  36. Pingback: sex offenders in my area montreal

  37. Pingback: Toys for Girls

  38. Pingback: ks pod

  39. Pingback: ดูบอลสด

  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 ;)|

  41. 응답

    Hi, I do think this is a great blog. I stumbledupon it 😉 I am going to come back once again since I bookmarked it. Money and freedom is the greatest way to change, may you be rich and continue to guide other people.

  42. 응답

    I have been browsing online more than 3 hours lately, but I by no means discovered any attention-grabbing article like yours. It’s lovely value enough for me. In my opinion, if all site owners and bloggers made excellent content material as you probably did, the net shall be a lot more helpful than ever before.

  43. 응답
  44. 응답

    Best online dating sites:
    [url=https://rocorontea.blogspot.com/2021/07/dating-seite-shemale.html]Dating Seite Shemale[/url] – NNorfolkpaul, 3rd December
    https://pinpongsa.blogspot.com/2021/07/trend-singlede-account-loschen.html
    https://hoftesali.blogspot.com/2021/07/weibliche-bekanntschaft-gesucht.html

    [url=https://progecyc.blogspot.com/2021/06/pdf-datei-erstellen-online-kostenlos.html]Pdf Datei Erstellen Online Kostenlos[/url]
    [url=https://kimudraen.blogspot.com/2021/07/online-dating-japanese-site-germany.html]Online Dating Japanese Site Germany[/url]
    [url=https://chiserso.blogspot.com/2021/07/bergen-dating-norway.html]Bergen Dating Norway[/url]
    [url=https://apsaladiso.blogspot.com/2021/07/online-dating-junge-christen.html]Online Dating Junge Christen[/url]
    [url=https://progecyc.blogspot.com/2021/06/free-dating-website-usa.html]Free Dating Website Usa[/url]
    [url=https://gobileme.blogspot.com/2021/07/online-dating-belgium.html]Online Dating Belgium[/url]
    [url=https://pinpongsa.blogspot.com/2021/07/][/url]
    [url=https://apcalelo.blogspot.com/2021/07/th-partnervermittlung-erfahrungen.html]Th Partnervermittlung Erfahrungen[/url]

  45. 응답

    Quality posts is the crucial to invite the people to pay a quick visit the site, that’s what this website
    is providing.

  46. 응답

    Excellent blog here! Additionally your site rather a lot up very fast!
    What web host are you the use of? Can I am getting your associate link to your host?

    I wish my website loaded up as quickly as yours lol

Leave a comment

이메일 주소는 공개되지 않습니다.