blog 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
SELECT column-names
  FROM table-name1
 WHERE value IN (SELECT column-name
                   FROM table-name2 
                  WHERE condition)

Subqueries can also assign column values for each record:
SELECT column1 = (SELECT column-name FROM table-name WHERE condition),
       column-names
  FROM table-name
 WEHRE condition
 

SQL Subquery Examples

Problem: List products with order quantities greater than 100.
SELECT ProductName
  FROM Product 
 WHERE Id IN (SELECT ProductId 
                FROM OrderItem
               WHERE Quantity > 100)
			   

SQL Subquery Examples

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 a correlated subquery because the subquery references the enclosing query (i.e. the C.Id in the WHERE clause).

  • Share This Story


Pleas give your valuable comments to improve the contents.

about author

Atul kumar shukla


I'm Atul Kumar Shukla, working as a web developer.
Welcome to my Blog. This is my personal blog where i post my own questions and answers , articles about ASP.Net., SQL Server, HTML, CSS, Jquery and many more..

Visit Profile

Leave a comment

Name
E-mail
Url
Message

Get More Stuff
Subscribing to the TheOneblogs newsletter and get intersting stuff and updates to your email inbox.
Your Email

 

Style Switcher

Change Color

×! We use cookies to improve your user experience. More information