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
- Generate a block of serial numbers from SQL Server while handling concurrent connections?
- Set column to some value if nested query returns null
- On changing connection with crystal report forms
- SSIS project fail to build - CPackage::LoadFromXML fails
- Sequelize issue interting rows on tables with triggers
- Availability rooms depending up on dates in SQL query?
- Importing tab-delimited text file - can't change the column mappings SQL Server 2008
- SQL server 2012 tables issue, sysdate and to_date issue
- Function not returing what I expect
- Analyzing time-dependent, event log from SQL
- SUM a field with distinct grouped records
- Why is fulltextsearch for phrase ignored in SQL Server?
- NullPointer exception when executing prepared statement
- Show SQL error in ASP.net
- left join with up to one row in the right table in LINQ
- How do I get dependent views to update automatically in SQL Server?
- Get Current week days from monday to sunday
- How do I loop through the results of a SELECT statement sql and use the result for stored procedure?
- Styling column entries in an email generated by SQL+HTML script
- SQL Primary Key Exception
- Select property from max in cross apply SQL
- subtract total counts from column in the previous row using SQL
- Nodejs mssql and PreparedStatement, excute not getting called
- How can I run multiple SSRS reports on multiple threads
- Stored Procedure - Select with parameters and fill textboxes
- fluent nhibernate nullable foreign key "null reference exception"
- SQL Get UID when Group by
- Exception: Operating System error code 3
- SQL server date convertion
- Writing CASE Statement in SQL
- SQL Server query with symbol (')
- SQL query for getting count for grouped items
- SQL MAX date in where clause
- How to get records from SQL Server for every hour only first record in a day in a month?
- sql assign a category id when using order by
- SQL to Extract all 30 minute or 60 minute time periods in a day that aren't reserved
- Pivot table with IN operator and WHERE clause
- DateDiff() on consecutive rows from a query using row_number()
- SQL Server error | Snapshot Isolation related
- Insert image into SQL Server
- Switch between databases, use two databases simultaneously
- Store SQL query message/Print Statement in file
- SQL: How to query whether a record exists in one table based on values in another table
- Alternative to SQL Server Management Studio "View Dependencies"
- Columnstore index - slow performance on offset-fetch query
- SQL Server Profiler showing ObjectType 20816 - PQ
- MSSQL delete code in visual studio with c# is running, but when I restart it returns to its original state
- How can I insert autoincrement value using SQL insert into select statement
- Group records when date is within N minutes
- Defining indexes: Which Columns, and Performance Impact?
- How to write a select Query inside a CASE condition in MS SQL
- SqlCeResultSet and SQL injection
- Is it possible to set a SQL server columns' description using a fluent nhibernate map?
- SQL Server GROUP BY and Filter Top 10%
- How can I handle a web application that connects to many SQL Server databases?
- Reordering data in a table
- ETL mechanisms for MySQL to SQL Server over WAN
- Intermittent SQL connectivity issue with AWS RDS
- Using sp_executesql with params complains of the need to declare a variable
- MS SQL Server: advanced substring, splitting one string column to multimle columns
- Is there a limit to how long a SQL query can be in SQL Server?
- Where And Where SQL
- How to use TSQL STUFF in a Pivot?
- Why does sp_columns return no results?
- Summerize Time in area
- Load Excel data into a views - SQL Server
- Shredding XML file is not returning all of the data
- How to express a BETWEEN statement in SQL when running query from a variable
- TSQL - Query to verify continuity
- Tags list in stackoverflow
- How to upload .sql type of files in laravel?
- Getting lowest level in a tree from any higher level with a self-join
- exception on inserting single quote in c#
- Sql Server deadlock error when performing multiple Inserts within a stored procedure
- how to enumerate all instances of SSRS, SSIS, SSAS, and SQL Server?
- Does the concatenation of NULL + 'something' always yield NULL? Why?
- Market Basket analysis -Apriori Algorithm Database Sample (Ms Sql Server)
- ADOX - tables in an SQL Server Schema not listed
- changing date format before insertion to database
- How should I test a field against an SQL variable which can be null?
- Connecting to SQL Server Express on AWS EC2
- Using GROUP BY to sum the values of columns in rows
- How to use union all when using order by clause SQL Server 2014
- SQL ID from table posts same ID to all other coldfusion entries per transaction
- SQL Server: Select and count
- DB design help - EAV + Form builder
- Access Build variables inside Pre and Post deployment scripts SSDT
- Subtract year and month
- The Difference Inner Join Query
- Binary Data Type in H2, Sql-Server, MySql
- UTF8 Error in SlashDB using SQL Server
- SQL Geography Intersect always returns 1
- How to efficiently stream video over HTTP directly from SQL Server?
- How to create a recursive counter with a Common Table Expression (CTE)?
- SQL Server PRINT SELECT (Print a select query result)?
- Pass large SQL statement to a variable in Python SQLAlchemy?
- AWS MS SQL create table
- SQL difficulty with COUNT and SUM in same query
- Why altering column from varchar(50) to int requires more space
- SQL how do I select a IDs which contains at least a certain condition and occurs more than once?
- Microsoft T-SQL Counting Consecutive Records
- SQL Parse String, got to be a better way
- What is the use of ASC keyword in SQL Server as ASC is the default?
- how to bind asp.net controls using a self join and items tables "hierarchically structure"?
- MSSQL Parameter consumes more time
- How do I create system versioned tables using SSDT in Visual Studio 2019?
- SQL Server: how to insert to temporary table?
- Getting Foreign Key Ids from Primary Key
- SQL server how to change pipe separated column string to rows
- Huge text or excel data Import FASTER into sql
- SP_Who does not return DBName Column
- How to Export from PHP to Excel (xlsx)
- How to create a SQL User-Defined Table Type identical to an existing one with a new name?
- TSQL Replicate row n times based on data
- Workaround for Spring/Hibernate due to non-standard behaviour of UNIQUE constraint in MS SQL
- Too many columns in single table - is it good normal form?
- Select Into #Temp not working with a PIVOT
- Convert byte[] array TO sqldbtype.Varbinary using Dapper
- Unable to get the Delphi EventAlerter sample working with SQL Server 2012
- sql server query partitioned table
- Using SSDT, how do I resolve SQL71561 errors when I have a view that references objects in a different database?
- Progress ODBC INSERT INTO fails after upgrade from 9.1E To 10.2B
- How to write tests for Entity Framework Sql providers and access the generated Sql command
- Query to pair interleaved start and end times
- Selecting top x categories with their top x products
- SQL Server 2016 - Array into separate columns
- Using T-SQL 'Two Digit Year Cutoff' in script
- Error in IQueryable + foreach
- How to sync multiple clients with a cloud database not platform specific
- Query Records and Group by a block of time
- how to substring an empty phrase in sql?
- How do I ensure our .NET Core APIs clean up database connections?
- What does the started keyword do in MSSQL?
- How can I use a dot/period in a column name with FOR JSON PATH without it creating a nested object?
- How to print the @SqlQuery annotation in JDBI sql api
- Practical Role Based Data Access Controls in ASP.NET MVC / SQL Server
- What's the difference between 'Client initiated aborted execution' and 'Exception aborted execution' in SQL query store?
- make python wait for stored procedure to finish executing
- how to Get previous three months date based on a from date and to date parameter value
- SQL Server compare numbers after decimal point
- Replace arabic Letter in database
- Reverse engineer a .NET SQL Assembly
- Save sql return in a string
- provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host
- SQL-Server XML-Bulk-Import and reading as table-data
- Condition WHERE clauses inside stored procedure
- SQL for Unique one column with order by date
- Select distinct row with max date from SQL Server table?
- "Invalid attempt to read when no data is present"
- Migrating MySQL procedure to SQL Server