Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Subqueries in sql - Part 59

Suggested Videos
Part 56 - Error handling in sql server 2005, 2008
Part 57 - Transactions
Part 58 - Transaction Acid Tests

In this video we will discuss about subqueries in sql server. Let us understand subqueris with an example. Please create the required tables and insert sample data using the script below.



Create Table tblProducts
(
[Id] int identity primary key,
[Name] nvarchar(50),
[Description] nvarchar(250)
)

Create Table tblProductSales
(
Id int primary key identity,
ProductId int foreign key references tblProducts(Id),
UnitPrice int,
QuantitySold int
)



Insert into tblProducts values ('TV', '52 inch black color LCD TV')
Insert into tblProducts values ('Laptop', 'Very thin black color acer laptop')
Insert into tblProducts values ('Desktop', 'HP high performance desktop')

Insert into tblProductSales values(3, 450, 5)
Insert into tblProductSales values(2, 250, 7)
Insert into tblProductSales values(3, 450, 4)
Insert into tblProductSales values(3, 450, 9)

Write a query to retrieve products that are not at all sold?
This can be very easily achieved using subquery as shown below. Select [Id], [Name], [Description]
from tblProducts
where Id not in (Select Distinct ProductId from tblProductSales)

Most of the times subqueries can be very easily replaced with joins. The above query is rewritten using joins and produces the same results. Select tblProducts.[Id], [Name], [Description]
from tblProducts
left join tblProductSales
on tblProducts.Id = tblProductSales.ProductId
where tblProductSales.ProductId IS NULL

In this example, we have seen how to use a subquery in the where clause.

Let us now discuss about using a sub query in the SELECT clause. Write a query to retrieve the NAME and TOTALQUANTITY sold, using a subquery.Select [Name],
(Select SUM(QuantitySold) from tblProductSales where ProductId = tblProducts.Id) as TotalQuantity
from tblProducts
order by Name

Query with an equivalent join that produces the same result.
Select [Name], SUM(QuantitySold) as TotalQuantity
from tblProducts
left join tblProductSales
on tblProducts.Id = tblProductSales.ProductId
group by [Name]
order by Name

From these examples, it should be very clear that, a subquery is simply a select statement, that returns a single value and can be nested inside a SELECT, UPDATE, INSERT, or DELETE statement. 

It is also possible to nest a subquery inside another subquery.

According to MSDN, subqueries can be nested upto 32 levels.

Subqueries are always encolsed in paranthesis and are also called as inner queries, and the query containing the subquery is called as outer query.

The columns from a table that is present only inside a subquery, cannot be used in the SELECT list of the outer query.

Next Video:
What to choose for performance? Queries that involve a subquery or a join

9 comments:

  1. Sir G ap to kamal hi kamal hain g
    a lot of thanks for this video it is really nice sir G

    Please sir G upload a tutorial of Reporting and crystal reporting sir G
    I am waiting eagerly !!!
    Ap ka student Liaqat Ali

    ReplyDelete
    Replies
    1. Hi Liaqat, Thank you very much for the feedback. I will have this in my to do list, and will do the needful as soon as I can.

      Delete
  2. hi venkat ur videos just superb...i'am so much got knowledge being watched ur videos...keep updating...Thanks a lot

    ReplyDelete
    Replies
    1. Hi Nagaraj, I am very glad these videos are helpful to you. I record and upload videos on a daily basis. If you want to receive email alerts when new videos are uploaded by me, feel free to subscribe to my channel at the following link.
      http://youtube.com/kudvenkat

      Link for C#, SQL Server, and ASP.NET Video Tutorial

      Delete
  3. Sir you are a Life saver, i am from mumbai please its a request that you open a branch in our city its a necessity for us.Thank you sir..

    ReplyDelete
  4. Hello Venkat Sir,, I am really appreciate your hard work that you have put for these vedios and slides.. I have learnt so many small small things from here.

    I want to thank you from my bottom of heart for this much of sharing. I always read comments of all and reading from you slides and i always found that how easily you make people understand just by your slides and videos you students are the luckiest one. Again Thank you so much.

    ReplyDelete
  5. In sub query how many number of columns can return

    ReplyDelete
  6. In sub query how many number of columns can return

    ReplyDelete
  7. hi venkat,
    i am very big fan of you, the way u r teaching, u r simple language is awesome.Thank you very much for u r tutorial.
    I am waiting for u r SSIS & SSRS technology tutorial.
    is there any plan to upload these tutorial series?

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.