How to Write Subquery in Select Statement in Sql

In this post, we will discuss subqueries in SQL Server. Let’s look into subqueries with an example.

We'll be utilizing the following tables to illustrate subqueries:

• TblProducts Table: Contains product-related information such as the product ID (acting as the primary key), product name, quantity, and price.
• TblProductSale Table: Stores product sales data.

Whenever a product is sold, a record is inserted into the TblProductSale table. This table includes fields such as ID (acting as the primary key), product ID (the ID of the sold product), the unit price at which it was sold, and the quantity sold.

Below is an insert script containing sample data for these two tables. You may use it for practicing subqueries if needed

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

If we look at the data in the TblProducts table, we find five products with IDs 1, 2, 3, 4, and 5: Books, Mobile Phone, Watches, Cameras, and Computer Accessories.

In the TblProductSale table, sales data is recorded. It's important to note the productID column. For instance, product with ID 1 (Books) has been sold twice, as has product ID 2 (Mobile Phone), while products with IDs 3 (Watches) and 5 (Computer Accessories) have each been sold once. However, product ID 4 (Cameras) has not been sold at least once.

Now, let’s consider a scenario where your project manager requests a query to retrieve the ID, name, and quantity of all the products that haven’t been sold at least once.

To determine if a product hasn't been sold at least once, we can check if its productID is not present in the TblProductSale table. If a product's ID doesn't exist in the productId column of the TblProductSale table, we can conclude that the product hasn't been sold at least once.

Therefore, to retrieve the name and quantity of all products that haven't been sold at least once, we need to select all products from the TblProducts table where their ID does not exist in the productId column of the TblProductSale table. It's as straightforward as that.

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

Utilizing a subquery here makes the query straightforward. The query enclosed within parentheses is referred to as a subquery.

Subqueries are always wrapped within parentheses. In this case, the subquery returns only one column, which is then employed in the WHERE clause using the 'NOT IN' operator.

Subqueries are also known as inner queries, while the query containing the subquery is referred to as the outer query.

Often, subqueries can be readily replaced with joins. We have extensively covered joins in our previous post series. If you are unfamiliar with SQL joins, I highly recommend reading the following post.

• What is Sql Joins? With Realtime Examples

So, let's rewrite the query to achieve the same output using joins.

In this case, we'll utilize a left join. However, with a left join, we obtain all the matching rows between both tables plus the non-matching rows. But that's not our desired outcome.

What we specifically want is only the non-matching rows from the left table. We are interested solely in 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

In this example, we have demonstrated the usage of a subquery within the WHERE clause, as well as how to replace that subquery with a join.

Both of these queries yield identical output. Let's explore another example of utilizing subqueries.

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

If we examine these two tables, my goal is to retrieve the name of each product along with the total quantity sold for each product. For instance, if we consider the quantity sold, we sold 10 Books and another 10 Books, totaling 20.

Now, let's proceed with the query. In this example, we previously demonstrated the usage of a subquery in the WHERE clause. However, in this case, we will explore how to incorporate a subquery in the SELECT list.

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

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 inform you that subqueries can be nested up to 32 levels.