In this article we will talk about subqueries in the SQL server. Let’s take sub-questions as an example.
Let’s take a look at the tables that will help us understand the sub-questions. The TblProducts table contains product information, such as the product ID, which also serves as the main key for this table, the product name, quantity and price, while the TblProductSale table contains the sales data for the product.
Every time we sell a product, an entry is made in the TblProductSale sales table. The TblProductSale table contains information such as the identifier that serves as the primary key for the table, the product, the identifier of the product we have sold, at what price per unit of product and in what quantity we have sold it.
This script adds some sample data to these two tables, so you can copy them if necessary for useful sub-questions.
Table Scenario
USAGE [Test Database]
GO
/****** Subject : Table [dbo]. Date of the scenario : 10/22/2020 12:38:00 ******/
ANSI_NULLED SETTINGS ON
GO
QUOTED_IDENTIFIER SETTINGS ON
GO
CREATE TABLE [dbo].TblProducts] (
[Id] [int] NOT NIL,
[product name] [nvarchar](100) NOT NIL,
[quantity] [int] NOT NIL,
[price] [margin] NIL,
CONSTRUCT [PK_TblProducts] APPLICABLE CLUSTER
(
[Id] ASC
)
) ON [PRIMARY]
GO
/****** Object : Table [dbo]. Date of the scenario : 10/22/2020 12:38:00 ******/
ANSI_NULLED SETTINGS ON
GO
QUOTED_IDENTIFIER SETTINGS ON
GO
CREATE TABLE [dbo].TblP Product Sales](
[Id] [int] NOT NULL,
[ProductId] [int] NULL,
[Quantity Sold] [int] NULL,
[Datetime] NULL,
CONSTRUCTION [PK_TblP Product Sales] APPLICABLE
(
[Id] ASC
)
) SETTINGS ON [PRIMARY]
YEAR
[dbo].[TblProducts] ([Id], [product name], [quantity], [price]) Price (1, N Books, 10, 100)
GO
INSERT [dbo]. [TblProducts] ([Id], [product name], [quantity], [price]) Price (2, N Mobile phone, 100, 15000)
GO
INSERT [dbo]. [TblProducts] ([Id], [product name], [quantity], [price]) Price (3, N watches, 50, 1000) INSERT
GO
[dbo]. [TblProducts] ([Id], [product name], [quantity], [price]) Price (4, N-camera, 30, 10000) INSERT
GO
[dbo]. [TblProducts] ([Id], [product name], [quantity], [price]) Price (5, N’Computer Accessories.40, 2000)
GO
INSERT [dbo] [TblProductSale] ([Id], [ProductId], [QuantitySold], [DateTime]) Price (1, 1, 10, CAST(N’2020-10-16T17:16:57.953′ AS DateTime).
GO
INSERT [dbo] [TblProductSale] ([Id], [ProductId], [QuantitySold], [DateTime]) Price (2, 2, 5, CAST(N’2020-10-16T17:16:57.953′ AS DateTime)).
GO
INSERT [dbo] [TblProductSale] ([Id], [ProductId], [QuantitySold], [DateTime]) Price (3, 1, 10, CAST(N’2020-10-16T17:32:44.040′ AS DateTime).
GO
INSERT [dbo] [TblProductSale] ([Id], [ProductId], [QuantitySold], [DateTime]) Price (4, 3, 10, CAST(N’2020-10-16T17:32:44.040′ AS DateTime).
GO
INSERT [dbo] [TblProductSale] ([Id], [ProductId], [QuantitySold], [DateTime]) Price (5, 5, 10, CAST(N’2020-10-16T17:32:44.040′ AS DateTime).
GO
INSERT [dbo] [TblProductSale] ([Id], [ProductId], [QuantitySold], [DateTime]) Price (6, 2, 10, CAST(N’2020-10-16T17:32:44.040′ AS DateTime).
GO TO
So if you look at the data in the table [TblProducts], you will see 5 products with the identification numbers 1, 2, 3, 4 and 5, books, mobile phones, watches, cameras and computer accessories.
TblProductSale, sales data in tabular form. Note the Product Identification column. Take a look at this. We have sold a product twice with an ID of 1. This means that we have sold books and mobile phones twice and only once watches and computer accessories. And if you look at the cameras, we didn’t even sell it.
Now suppose your project manager asks you to write a request that returns the ID, name and quantity of all products we have not sold at least once.
How can we say we didn’t sell the goods at least once?
If you look at the table TblProductSale, if there is no productId, you can safely say that we have not sold this product at least once.
So if we want to know the name and quantity of all products that have not been sold at least once, we want to know all products in the [TblProducts] table where this identifier does not appear in the productId column of the [TblProductSale] table. It’s as simple as that.
Select the ID, product name, quantity, price
in [dbo] [TblProducts]
if the ID is not specified (select the distinguishing product ID in [dbo] [TblProductSale]).
Take a look at this. How easy it was to use the subquery here. Therefore, the request is referred to in brackets as a sub-application.
Subqueries are always in parentheses and take the subquery into account. Only one column is returned, and this column is now used in your article where, and we don’t use it in the article in
Subquestions are also called internal questions. And the query that contains the subquery is called an external query.
Nowadays, sub-applications can very often be easily replaced by connections. We talked about the Join in the Join series. If you are not familiar with SQL code, I strongly recommend that you read the following message
So let’s rewrite the query, which will give us exactly the same result with the connections.
We want to use the left connection here. If I use a link connection, I get all the matching rows between the two tables plus the wrong row, but that’s not what we want.
We only need the inconsistent rows in the left table. All we need is misplaced lines.
How to specify it using the item in [TblProductSale], where ProductID is NULL.
Select TblProducts.Id, product name, quantity, price
of TblProducts
. On the left side connect TblProductSale
to TblProducts.Id = TblProductSale.ProductId
where TblProductSale.ProductId is NULL.
So in this example we have seen how to use a subquery where it is located and we have also seen how to replace that subquery using the join.
The two requests lead to exactly the same result. Let’s look at another example of the use of subqueries.
Apply for the name and total number of items sold.
So if you look at these two tables we have, I want the name of the product and the total amount of each product we sold. So, if we look at the quantity sold, how many books did we sell? 10+10=20.
Let’s see how we’re gonna do this. In this example we have seen how this subquery is used to the point. In this example we see how the subquery is used in the selection list.
Select the product name,
(select TOTAL (quantity sold) from the TblProductSale
table, where ProductId = tblProducts.Id) as the total quantity
from the tblProducts
table, sorted by product name.
Same result with memberships
Select the product name, TOTAL (quantity sold) as total quantity
in tblProducts
on the left side and connect tblProductSale
to tblProducts.Id = TblProductSale.ProductId
Group by product name.
Let me say that subqueries can be nested up to 32 levels.
Related Tags:
