A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.
Example:
----Example of Correlated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
)
GO
A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.
Example:
----Example of Noncorrelated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE c.Title = 'Mr.'
)
GO
Both of above subqueries can be written using Joins, Exists, In clauses. However, I have tried to demonstrate two different kind of subquery examples. Let me know if you have any questions or wants me to elaborate on this subject.
Reference : Pinal Dave (https://blog.sqlauthority.com)
75 Comments. Leave new
You are still my hero, thnx.
Hi Dave,
I need to link two tables without duplication. Suppose if the table A & B are.
(A) (B)
Aid Aname Bid Bname
1 ravi 1 sundar
1 ram
1 raj
The result should be,
Aid Aname Bname
1 ravi Sundar
1 ram
1 raj
Is it possible to get the result by Joins without Temp table?
Pls. help me to sort out the issue. if it possible pls send me to my email.
Thanks in advance.
select t1.Aid,t1.Aname,t2.Bname from A as t1 left join B as t2 on t1.Aid=t2.Aid
hi
can u tell me how can i write query for multiplication cumulative?
example input values
id month value
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
month(1)* value (1)=1
month (2)* previous result(1)=2
month (3)* previous result(2)=6
month (4)* previous result(6)=24
expected output
month multiplied cumulative
1 1
2 2
3 6
4 24
5 120 like this
@muthu
Use this script.
— This is your original table
Create table @Temp (id int, month int, value int )
insert into @Temp values (1, 1, 1)
insert into @Temp values (2, 2, 2)
insert into @Temp values (3, 3, 3)
insert into @Temp values (4, 4, 4)
insert into @Temp values (5, 5, 5)
— Lets declare a table variable which will be used for our calculations
— and a Output table Variable which will be used to store results
Declare @Cal_Table1 Table (Ident Int Identity , Month int , Value int )
Declare @Output_Result Table (Month int , Value int )
Insert Into @Cal_Table1 (Month, Value)
Select Month
, Value
From @Temp
order by Month — This is important for correct results
Declare @Count int
, @Count_Sum int
, @Previous_Val_Result int
Select @Count_Sum = SUM(1)
from @Cal_Table1
Set @Previous_Val_Result = 0
Set @Count = 1
While @Count < = @Count_Sum
Begin
If @Count = 1
Select @Previous_Val_Result = MONTH * Value
From @Cal_Table1
Where Ident = @Count
Else
Select @Previous_Val_Result = MONTH*@Previous_Val_Result
From @Cal_Table1
Where Ident = @Count
Insert into @Output_Result (Month, Value) Values (@Count, @Previous_Val_Result)
Set @Count = @Count + 1
End
Select * from @Output_Result — This is your final Result.
Let us know if you have questions.
~Peace
Hi Pinal ,
I am new to sql server , used to read you blog.
I have one requirment , can i use like operator for more than one value, in a subquery of a select stmt .
Give us more details on what you want to do
Is there any way to work around the limitation of not being able to reference into a subquery? For instance, I’m selecting a number of fields for my main query and I would also like to select several fields from the sub in that same main select statement.
Can you explain the internal functional behaviour of SQL in subquery and correlated-subqueries. e.g. :
the above given example of yours :
will the subquery be executed everytime new row fetched ? from outer query ?
or subquery is calculated and then outer query retrieves rows and evaluate against selected rows ?
Also what happens when outer row determines the contents of correlates subquery ? whats is the sequece in that time ?
Can you explain the internal functional behaviour of SQL in subquery and correlated-subqueries. e.g. :
the above given example of yours :
will the subquery be executed everytime new row fetched ? from outer query ?
or subquery is calculated and then outer query retrieves rows and evaluate against selected rows ?
Also what happens when outer row determines the contents of correlates subquery ? whats is the sequece in that time ?
Dear Sir,
I finished MCA in 2009 batch…Now only i joined software field.Sir I want to develop my skill(Sql Server). Please tell how to contact you or otherwise send the sql tricks to my mail id..
Thank you Sir
Dear,
Result will be same of both of subquery,
but Noncorrelated subquery Perform faster
Dear sir,
your blog is really nice.
you are the best in explaining the concepts…
Thank you
Sir,
Thanks for this solution . I would like to know how to call web service from stored procedure…please let me know.
Hi Pinal,
I wish you very Happy life,who is helping lot of People in this world thrg this site.
I have one Question:
what is limitaion of correlated subquery?
I can i optimize below query?
select empid,name,Deptid,
Dept_EmpCount=(select count(*) from emp e1 where e.deptid =e1.deptid)
from emp e
in this Query i am retrieving EmpId,Name ,DeptId, and number of employee in his dept .
I want to OPtimize this Query.
Please Help!!!
Regards,
Dinesh Babu (SQL DBA)
Have you tried this?
select e.empid,e.name,e.Deptid, count(*) as Dept_EmpCount from emp e inner join
(select deptid,count(*) as Dept_EmpCount from emp group by deptid) as e2 on e.deptid=e2.deptid
Hi Madhivanan,
Thanks for Reply!!!
your answer look good to me but we need to use in your query group by clause if we are using count(*)
but i want to do same think from Correlated sub query in optimized way…
Thanks,
Dinesh Babu
Compare the difference in execution time and choose the best one
Hi All,
can we have multiple MDF(primary data file) in one database?
Thanks,
Dinesh Babu
Every database can have only one primary file .However you can add as many secondary files as possible
Hello Sir,
I have one question how can we use the order by clause in the Sub queries.
Regards,
Raghav Sharma
Yes as long as you use top operator too
Hi Dave,
For the Querf Of Pawan, for intechanging the values in a column u have given a good solution for it by making use of single query,but it works fine when both the columns are of same datatype,bt its nt wrking wen we use different types,so any conversions?
Regards,
RAM.
Can you give us an example of what you are referring?
respected sir ca n u tell me how to find out the increment in salary (percentage) from the table????
Can a subquery be used in an insert statement?
As:
insert into ______()
VALUES()
Is the above valid?
Yes like below
insert into……….
Hi Pinal,
The information in the site is very useful for us.
Can you please send some good sql tutorials( ebooks) for 3+ years exeprienced in .net tecnologies, as I have not worked much in db part.
Would like to improve on that
Hi,Madhivinan
this is the scenario wher i had a doubt of interchanging the columns?
So any solution?
create table demotable(id int,name varchar(120))
Insert demotable values(1,’abc’)
Insert demotable values(1,’def’)
select * from demotable
update demotable
set id = name,
name = id
Why are you interchanging the columns? Note that both should have same datatype decdlared to do this
Hi ,
in SQL can you explain me some security loop holes which can access the program and destroy it?
Thanks & Regards
Nikita