In this post, we’ll discuss subqueries in SQL server. Let’s understand subqueries with an example.

Let’s look at the tables that we’ll be using to understand subqueries. TblProducts Table contains product-related information like the id of the product, which is also going to act as the primary key for this table, name of the product, Quantity and Price of product and TblProductSale Table contain product sales data.

1
Every time we sell a product, an entry will be made into TblProductSale table. TblProductSale table contains information like ID, which is going to act as the primary key for the table, productid, the Id of the product that we sold, at what unit price did we sale and how much quantity did we sell?

This insert script inserts some sample data into these two tables. so you can copy it for practice subqueries if required.

Table Script

USE [TestDatabase]
GO
/****** Object: Table [dbo].[TblProducts] Script Date: 10/22/2020 12:38:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProducts](
[Id] [int] NOT NULL,
[ProductName] [nvarchar](100) NOT NULL,
[Quantity] [int] NOT NULL,
[Price] [float] NULL,
CONSTRAINT [PK_TblProducts] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblProductSale] Script Date: 10/22/2020 12:38:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProductSale](
[Id] [int] NOT NULL,
[ProductId] [int] NULL,
[QuantitySold] [int] NULL,
[Datetime] [datetime] NULL,
CONSTRAINT [PK_TblProductSale] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (1, N'Books', 10, 100)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (2, N'Mobile Phone', 100, 15000)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (3, N'Watches', 50, 1000)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (4, N'Cameras ', 30, 10000)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (5, N'Computer Accessories.', 40, 2000)
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (1, 1, 10, CAST(N'2020-10-16T17:16:57.953' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (2, 2, 5, CAST(N'2020-10-16T17:16:57.953' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (3, 1, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (4, 3, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (5, 5, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (6, 2, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO

So if you look at the data in [TblProducts] table, there are 5 products with Id 1,2,3,4,5, Books, Mobile Phone, Watches, Cameras, and Computer Accessories.

TblProductSale, table content sales data. Pay attention to productID column. Look at this. We have sold the product with Id equal to 1 two times. That is, we sold Books and Mobile Phone 2 times and Watches and Computer Accessories only once. And if you look at Cameras, we haven’t sold it at least once.

2

Now let’s say your project manager is asking you to write a query that returns the ID , name and Quantity of all the products that we haven’t sold at least once.

Now, how can we tell if we haven’t sold at least once a product?

If you look at the TblProductSale table, if the productId is not present there, then we can say for sure we haven’t sold that product at least once.

so if we want to retrieve the name and quantity of all the products that are not sold at least once, we want to retrieve all the products from the [TblProducts] table where that ID does not exist in the productId column, in [TblProductSale] table. It’s as simple as that.

Select Id, ProductName, Quantity,Price
from [dbo].[TblProducts]
where Id not in (Select Distinct ProductId from [dbo].[TblProductSale])

subqueryresult

Look at that. How easy it was to use subquery here. So the query that is present inside parenthesis is called a subquery.

Subqueries are always enclosed within parentheses and look at the subquery. It returns only one column. and this column is now used in your where clause and we are using it in “not in”.

3

Subqueries are also called inner queries. And the query that contains the subquery is called an outer query.
4

Now many times the subqueries can actually be very easily replaced with the Joins. We have discussed Joins in the in join post series. If you are not familiar with SQL joins, I would strongly encourage you to read the below post.

so let’s re-write the query, which gives us exactly the same output using Joins.

We want to use the left join here. when I use left join, I get all the matching rows between both the tables plus the non-matching row, But that’s not what we want.

What we want is only the non-matching rows from the left table. we only want the non-matching rows.

How do we specify that using a where clause in [TblProductSale], where ProductID is NULL.

Select TblProducts.Id, ProductName, Quantity,Price
from TblProducts
Left Join TblProductSale
ON TblProducts.Id = TblProductSale.ProductId
where TblProductSale.ProductId IS NULL

so in this example, we have seen how to use a subquery within the where clause, and we have also seen how to replace that subquery using a join.

both of these queries give us exactly the same output. Let’s look at another example of using subqueries.

Write a query to retrieve the name and total quantity sold.

So if you look at these two tables that we have, I want the name of the product and the total quantity of each product that we have sold. So if we look at the quantity sold, how many Books did we sell?10+10=20

Let’s see how to do that. In this example, we have seen how to use this subquery in the where clause. In this example, we’ll see how to use a subquery in the select list.

Select ProductName,
(Select SUM(QuantitySold) from TblProductSale
where ProductId = tblProducts.Id) as TotalQuantity
from tblProducts
order by ProductName

6

Same result using Joins

Select ProductName, SUM(QuantitySold) as TotalQuantity
from tblProducts
left join TblProductSale
on tblProducts.Id = TblProductSale.ProductId
group by ProductName

Let me tell you that subqueries can be nested up to 32 levels.

The post How to write subquery in select statement in Sql appeared first on Software Development | Programming Tutorials.



Read More Articles