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
- Using subquery alias in case
- T-SQL Inner Join
- SQL Server, join multiple columns in a row with sum up
- Microsoft Sql Server management studio not recognizing unicode Kannada Font
- SQL Powershell Error: Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet
- What is regexp_replace equivalent in SQL Server
- Best solution for optional parameter in stored procedure
- Getting only new data from SQL Server
- How can I take flat rows of relational data and put these into a C# class?
- SQL Query to Retrieve Multiple Varients based on ranges of inputs and existing data
- Tsql Null value missing
- Error creating a table : "There is already an object named ... in the database", but not object with that name
- Index on VARCHAR column
- Excel rendering error in SSRS
- SQL Server query LIKE doesn't work for string
- Perform a double sort on two columns in SQL Server with a primary sort not in the evaluation of the first two
- 'IF' does not prevent error, but only if it has already executed
- SQL Convert each date range into each day row
- Opening a binary stream (such as a PDF) in the browser
- ColdFusion not maintaining connection to Azure Data Warehouse
- Generate list of dates based on the day and occurrence of the month
- Is CONVERT non-deterministic?
- Insert update delete trigger
- SQL Server comparing mechanism
- SQL - Return records with highest version for each quotation ID
- How can I insert values as a distinct column?
- Inline scalar functions: real or vaporware?
- How to compress a file using SSIS that then goes to Azure data lake (without exporting flat files first)
- Performance of ON Clause over WHERE Clause
- Passing nullable types to SQL Server CLR function - 2008 only
- Pivot multiple columns based on one column in SQL Server
- Undocumented stored procedures in MS SQL, why?
- Convert date to yyyy-mm-dd from c# to sql server
- How to populate a SQL table column only the first time
- SQL Server 2008 linked server connection string setup
- Where condition in SQl to filter the second part of string to be an integer
- Displaying multiple images in one datalist template cell
- Sum up values from databases and parametrize it. [SQL Server]
- SSRS Error - "Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate
- MSSQL error (saying not enabled when it is) using WAMP / PHP 5.3.4
- Relative Performance in SQLServer of Substring vs a Right-Left combo
- Is there a way to pin query result in 'Microsoft SQL Server Management Studio'
- getting attendance records from database into gridview ASP.net C#
- Remove string portion from inconsistent string of comma-separated values
- Delete duplicate rows except one
- Having Asking for Group By clause and not allowing it
- How do you test your software on possible sql server deadlocks before production?
- Unable to upload Image to database due to Base64 error
- java is not catching ms sql stored procedure raise error
- How to perform read data from file, then validate data and insert data in to Database operations asynchronously
- Views in SQL Server
- Is the 'WITH' clause only syntactic sugar?
- Is there any way to replicate a memory can't be read error message in my C# application?
- Opening a SQL Server .bak file (Not restoring!)
- Roundup years obtained from datediff function
- Multiple counts on a single column SQL
- Doctrine 2 Generates SQL from Entity Metas not explicit NULL on attributes nullable=false on SQLServer
- SQL Join two tables and select a matching record at random from the second table
- Cannot restore mssql database because .mdf file is missing
- SQL Server, getting "for xml" value
- SSRS report subscription ended with an error
- Fetching data from two tables matching a criteria
- SQL server bi-directional replication and auto-increment primary key
- Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ',' trying to INSERT INTO
- How to get level and count for item in recursive query
- How to send update batch with statements simultaneously in SQL
- How to convert DateTime to TimeSpan in Entity Framework query
- Behavior of a loop within a single TRANSACTION
- Why this LEFT OUTER JOIN is not including all the Primary Keys from the Left
- Percentage from Total SUM after GROUP BY SQL Server
- Reverse engineering check constraints & indexes w/ Hibernate?
- Moving data in SQL Server using where table1.jobid = table 2.jobid
- Need to update table row from UDF in SQL Server
- SQL Case Statement in set; Does it always fire?
- Node.js and Microsoft SQL Server
- SQL formatting standards
- How to create an sql command that has SELECT statements from 2 different tables?
- MS SQL correlated sub queries, Unable to write outer query
- DateTime conversion error when querying SQL
- ExecuteNonQuery is not working to create TempTable SQL Server
- Two same queries to find duplicate records return diffrent result
- adding a constraint that checks existence of stored proc
- angularjs: how to cascade 2 dynamically created dropdown lists
- query two table using one parameter pdo
- Edit sql field type
- ODBC Data Sources; can't add MySQL Driver
- Can we convert all SQL scripts to Linq-to-SQL expressions or there is any limitation?
- C# How do I copy tables and stored procedures to an empty mdf file
- Query data from meta data driven schema similar to EAV
- If Record Exists, Update Else Insert
- How does a SqlCommand communicate with Sql Server, in detail?
- SQL Server JSON retrieval based on specific selection criteria
- Remove successive row with same fields value
- Selecting a date based upon non unique column data in SQL Server
- Why are my buffered points oblong and not circle in SQL Server Management Studio - Spatial Results
- how to use for each loop in sql server?
- How to convert columns to row sql
- Update multiple rows and columns using respective ids in mssql
- Selecting Columns A values if no like a value in column B in SQL Server
- SQL: how to check for a specific DateTime
- comparing two tables
- Improve performance against sys tables in SQL Server
- Creating a table using explicit create table statement versus select into
- Create user on database level
- If I open a Cursor in side a TRY then where should I close it?
- Limit the acceptable range of a value in SQL Server 2008
- SQL Server - why is scanning done twice for the same table?
- Update Value without cursor
- Number of times a particular character appears in a string
- login issue with SQL Server
- SQL Server Create multiple foreign keys
- empty recordset values - sql server 2017
- How to properly use pivot funciton to combine multiple values (or columns) into within one column in SQL Server?
- MSSQL - How to set default value in BULK INSERT?
- SQL TRIGGER handling multiple INSERTED rows
- get a new table from two tables based on the columns' values in a table in SQL server:
- Insert into a table in another database after first deleting
- SQL Server Free Text search: search words from a phrase in two tables
- Error while setting up a connection with Oracle Connection Manager in SSIS
- Bad IL format - Strange .NET error
- Grant access to master db
- What happens to a SQL Server 2005 table with an identity (int) that goes over maxint?
- Case Statements using variables not working
- Data Driven Subscriptions SSRS Standard Edition 2008
- Update older records when a new column is added
- How do you think while formulating Sql Queries. Is it an experience or a concept?
- How to copy records from parent to child in same table using MS SQL CTE
- MYSQL- Create and store primary key in master and child table
- How to query SQL Server XML column
- SQL Server If statement woes
- In EF Code First is there a data annotation for description field?
- SQLCMD command, How to save output into log file
- SQL convert column to comma separated row
- Is there a way to programmatically convert a SQL Server query plan to an image?
- T-SQL Query only invoices between Start Date and End Date
- When to keep an index - Analysing index usage stastics in SQL Server 2008
- How to populate Excel ComboBox with data from SQL Server?
- Converting flat file 8859-1 encoding to OLE DB Destination using codepage 1252
- How to call a calculated column in "from" in sql server
- Count for groups and sub-groups
- pyodbc connecting to SQL Server 2012 problems
- Execute A Dynamic SQL statement Stored in a Column of a table
- Is it possible to remove the cursor from this dynamic query?
- Sum of row in sql with average and difference
- How to multi-select filter an EAV table in SQL Server
- How to get the most recent row in a temporal tables history
- Complex model binding in ado.net
- Multiple Like SQL Command
- SQL Insert into Select failure due to one of the set of records
- sql temp table join between servers