Hello, welcome to Apploveworld Technologies. In this article, we discuss the subquestions related to Cor in the SQL server.
In the previous research cycle, we discussed the basis for the sub-questions. We also discussed how partial questions can be replaced by connections. If you have not read this message, I strongly advise you to read it before continuing.
The correlation subquestions are very simple and clear. If the values of the subqueries depend on the external query, this subquery is called the correlation subquery.
Let’s first look at subquery, which is not related to the subquery on corrupt persons.
In this series we will use the same tables as in the previous article, TblProducts and TblProductSale.
The TblProducts table contains product data and the contents of the TblProductSale table that relate to sales, so every time we sell a product, a record is created in the TblProductSale table.
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 ZERO,
[product name] [nvarchar](100) NOT ZERO,
[quantity] [int] NOT ZERO,
[price] [margin] ZERO,
CONSTRUCT [PK_TblProducts] APPLICABLE
(
[Id] ASC
)
) TO [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
Let’s just say I want all products that haven’t been sold at least once. How do I write this request? If we make a sale and listings in the TblProductSale sales table, and if you look at the sample data we have, we obviously haven’t sold a camera at least once.
Select the ID, product name, quantity, price
in [dbo] [TblProducts]
if the ID is not specified (select the distinguishing product ID in [dbo] [TblProductSale]).
Pay attention. How does this application work?
The subquery is executed first. So, if you look at this query, you can make the subquery yourself.
This is an example of a partial aspect that has nothing to do with the company. If the subquery is not corrupted, the values in the subquery are independent of the external request. You can do the subquery yourself. After the query has been performed, the results of the subquery are inserted between these brackets for use by the external query.
Let’s take another look at the example of a disordered sub-question using the same examples we discussed in the previous session.
For example, let’s say I want to know if all the products we have sold and the total quantity.
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.
I want to summarize the amount of goods sold for each of them. The product identification comes from the TblProducts-Desk.
So every time we select a string in an external query, a subquery is now executed.
if you’re looking at it now, can I ask a sub-question aside from the external question?
No, I can’t look at it. If I try to do that, I’ll make a mistake.
4104, level 16, status 1, line 7
Multi-component designation tblProducts.Id cannot be paired.
It is thus a correlated subquery, because the subquery depends on the external query for its value, for the value of the productId column.
This sub-application depends on an external request for its value, in this case for the productId. Therefore, the subquery here depends on an external query, so the subquery is correlated with the subquery.
Correlation subqueries are performed for each line selected by an external query in our selection operator. A correlating subquery cannot be performed independently of an external query, while an uncorrected subquery can be performed independently of an external query.
Related Tags:
