asp.net core docker Subqueries in sql Part 59

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

Text version of the video
http://csharp-video-tutorials.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatAr...

asp.net core docker Subqueries in sql Part 59
837 Likes	837 Dislikes
235,798 views views	524K followers
Education	Upload TimePublished on 7 Jan 2013

Related keywords

wcf vs web api,ado.net core,sql server management studio,webkinz,webadvisor,craigslist nj,wcf one piece,asp.net core 3,ajax players,weber grill parts,webtoon,tutorials by hugo,csharp corner,mvc design pattern,asp.net machine account,servers for minecraft,asp.net core dependency injection,ado.net tutorial,services angular,subqueries in mysql,ajax ontario,subqueries sqlite,asp.net mvc tutorial,asp.net cos'è,csharp assembly,tutorialspoint python,sql join,services briefcase,asp.net core web api,ajax jquery,wccftech,craigslist ny,asp.net zero,csharp foreach,server memes,sql date format,services online,chase,serverless architecture,server resume,wcf c#,server books,tutorialspoint javascript,mvcc connect,ado.net mysql,services technologies gps,ajax deadpool,server jobs,cvs,website,mvc tutorial,costco hours,wcf service application,tutorialspoint spring,serverless,wcf soap,sql server connection string,sql server tutorial,wcf cat,wcf test client,cool math games,services & training hse colombia sas,servicestack,citibank,asp.net core identity,sql union,ajax parking,sql database,asp.net core logging,mvconnect,asp.net guida,cunyfirst,subqueries in teradata,wcf nba,csharp download,wcfi foundation,csharp online,wcf authentication,subqueries in select statement,tutorials near me,http://asp.net,server 2019,ado.net descargar,web of dreams,chernobyl,serverminer,ajax cleaner,ado.net visual studio 2019,webassign,ado.net vs entity framework,ado.net visual studio 2017,csharp list,sql like,subqueries in access,asp.net mvc,asp.net core tutorial,sqlite,wcfm,ajax roster,mvc architecture,http://ado.net,asp.net core mvc,ajax soccer,sql server configuration manager,server hosting,wcfi,ajax dish soap,capital one,subqueries cannot be used in the expression,server rack,tutorialspoint html,csharp interface,craigslist,sql server download,webroot,tutorialspoint reactjs,ajax request,wcf dragon ball,asp.net core 2.2,tutorialspoint python 3,sql developer,webster,sql server date format,services transmission company sas,sql group by,asp.net core signalr,services manager,mvc framework,ajax paving,mvc near me,tutorialspoint spring boot,mvc map,csharp online compiler,subqueries w3schools,asp.net download,sql between,ado.net c# pdf,wcf 2019 nba,services tag dell,csharp switch,ado.net ventajas y desventajas,csharpstar,wcf tutorial,sql server 2016 download,tutorialspoint,ajax meaning,csharp-video-tutorials.blogspot,tutorials dojo,csharp string format,central park 5,ado.net c#,asp.net core github,subqueries vs cte,server status,ajax fc,server jobs nyc,asp.net core swagger,sql formatter,credit karma,services group,subqueries in where clause,sql server 2008,server error in '/' application,services windows,asp.net core 3.0,sql injection,wcf ria services,tutorialspoint c#,calculator,ado.net entity data model,sql insert,tutorialspoint tableau,services google play apk,sqlyog,asp.net core 3 release date,sql server,server job description,tutorials by a,servicenow,webcam,mvc hours,webmd symptom,csharp array,csharp enum,ajax call,asp.net core 2. guida completa per lo sviluppatore,asp.net core,server pro,server status ffxiv,webcrims,cheap flights,asp.net core hosting,services sas,subqueries with joins,tutorialspoint java,tutorialspoint java compiler,subqueries in postgresql,subqueries in soql,subqueries in hive,webmd,subqueries in sql server,sql server 2014 download,csharp to json,college football,ado.net dataset,csharp dictionary,subqueries vs joins,cnn,website builder,tutorialspoint sql,asp.net web api,server side rendering,weber grills,sql server 2014,sql server 2016,sql server 2017,mvc nj,sql server 2012,tutorialspoint spark,ado.net oracle,asp.net core download,csharp to vb.net,sql server 2019,webster bank,webstaurant,tutorialsystems,ajax post,services fms publish announcement,services.msc no abre,ajax jersey,csharp operator,asp.net core razor pages,subqueries in sql,server duties,asp.net core environment variables,csharp random,century 21,sql server data tools,services consultores,services consulting,mvcsd,mvcsc,services.msc,ado.net pdf,asp.net core configuration,ajax marvel,sql update,asp.net tutorial,mvc medical,ado.net entity data model visual studio 2019,wcfs international curriculum,mvc2,subqueries in spark sql,ado.net entity data model visual studio 2017,subqueries in snowflake,chase online,wcf api,costco,server jobs near me,webex,sql meaning,sql tutorial,sql server express,subqueries in oracle,sql commands,sql server 2008 r2,ado.net entity framework,ajax tavern,sql server versions,tutorialsteacher,ajax javascript,services desk,subqueries examples,ajax greek,csharp tutorial,mvc pattern,ado.net sql server,ado.net connection,asp.net identity,mvcu,asp.net core middleware,sql server management studio 2014,wcf web service,mvc webadvisor,web store,mvcc,webmail,mvci,mvctc,sql server management studio 2012,