What is difference between Subquery and Correlated subquery

Hello and welcome to Quickpickdeal Technologies. In this post, we will delve into Correlated Subqueries in SQL Server.

In our previous post on subqueries, we covered the fundamentals of subqueries. Additionally, we explored how subqueries can be replaced using joins. If you haven't read that post yet, I highly recommend doing so before proceeding with this one.

• How to write subquery in select statement in Sql

Correlated subqueries are straightforward and easy to comprehend. A subquery is considered correlated if it relies on values from the outer query for its execution.

Non-Correlated subquery

We will utilize the same tables as in the previous post of this series: TblProducts and TblProductSale.

The TblProducts table stores product information, while the TblProductSale table contains sales-related data. Each time a product is sold, an entry is recorded in the TblProductSale table.

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

Now, suppose I want to fetch all the products that have not been sold at least once. How would I construct such a query? Clearly, whenever a sale is made, entries are added to the TblProductSale table. Upon examining the sample data, it's evident that Cameras haven't been sold at least once.

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

Now, look at this. how does this query gets executed?

The subquery is executed first. In this query, the subquery can be executed independently.

This is an example of a non-correlated subquery. In a non-correlated subquery, the subquery is not reliant on the outer query for its values. The subquery can be executed independently. Once the subquery is executed, its results are then substituted within the parentheses for use by the outer query.

Now, let’s look another example of a non-correlated subquery using the same examples discussed in the previous session.

Suppose, for instance, I wish to determine all the products that have been sold along with their total quantity.

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

I want to calculate the total quantity sold for each product. Therefore, the productID will be retrieved from the TblProducts table.Now, every time a row is selected from the outer query, the subquery is executed.

However, it's important to note that I cannot execute the subquery independently of the outer query. Attempting to do so results in an error."

Msg 4104, Level 16, State 1, Line 7
The multi-part identifier “tblProducts.Id” could not be bound.

That constitutes a correlated subquery because the subquery relies on the outer query for the value of the column 'productId'.

In this case, the subquery depends on the outer query for its value, specifically for the 'productId'. Thus, the subquery is correlated with the outer query.

Correlated subqueries are executed for each row selected by the outer query in our SELECT statement. Unlike non-correlated subqueries, correlated subqueries cannot be executed independently of the outer query.