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.
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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?