What is difference between subquery and correlated subquery

Hello, welcome to Apploveworld Technologies, In this post, we’ll discuss Correlated subquery in SQL server.

In the previous post subquery series, we have discussed the basics of the subquery. We also discussed how subqueries can be replaced using join. If you haven’t read that, I would strongly encourage you to read that post so before continuing with this post.

• How to write subquery in select statement in Sql

Correlated subqueries are very simple and easy to understand. if the subqueries depend on the Outer Query for its values, then that subquery is called Correlated subquery.

First Let’s look at Non-Correlated subquery

We will be using the same tables that we have used in the previous post of this series, TblProducts and TblProductSale.

TblProducts Table contains the product data and TblProductSale table content sales-related data.So every time we sell a product, an entry will be made into 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 let’s say I want to retrieve all the products that have not been sold at least once. How do I write that query? Obviously, whenever we make a sale and entries made into the TblProductSale sales table, and if you look at the sample data that we have, we haven’t sold a Cameras 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 gets executed first. So if you look at 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 dependent on the outer query for its values. The subquery can be executed independently on its own. once query is executed, the results of the subquery are then substituted within those parentheses for use by the Outer query.

Now let’s look at an example of a non-correlated subquery again will be using the same examples that we have discussed in the previous session

Let’s say, for example, I want to find out, all the products that we have sold and the total quantity.

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

I want to sum up the quantity sold for each product. So the productID is going to come from the TblProducts table.
So every time we select a row from the outer query, now the subquery gets executed.

if you look at this now, can I execute subquery independently outer query?

No, I can’t look at this. When I try to execute that, I get an error

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

So that’s a correlated subquery because the subquery is dependent on the outer query for its value, for the value, for column productId.

this subquery dependent on the outer query for its value, in this case for productId. So here the subquery is dependent on the outer query for its value hence subquery correlated subquery.

Correlated subqueries get executed for every row that is selected by the outer query in our select statement. A correlated sub query cannot be executed independently of the outer query, but whereas a non-correlated subquery can be executed independently of the outer query.

The post What is difference between subquery and correlated subquery appeared first on Software Development | Programming Tutorials.