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
Sir,
I saw your website its really super it very help full to beginners and improve our knowledge this website because it can easily to understand them.
I have lots of doutes in the sql server pls send to my mail -id daily some tips and tricks in sql server sir.
Also please suggest some books in sql server and how can i get into some company as a sql developer.
plz if possible send the SQL interviw questions.
Thanks,
T.Manikuma
Hello sir,
I want to get the list of empno’s and ename who is working in NEW YORK location using non-correlated subqueries using emp and dept tables…could you let me know the sql query for this?
We all love your articles ! Its really precious as gold !!
Thanks and regards
R Jagtap
Pinal pls help me out . if a table contains some columns with id say 1,2,3 if i delete row at 2. now i want to insert new row at the id 2
thank you in advance
Its Good
The article which you have given us is very good and easy understandable please send some of the articles to my mail id .i still like to learn more tips in sqlserver
Hi Pinal,
select C1 from TA where C1 in (Select C1 from TB)
The column C1 does not exists in the table TB. but this query still qworks and all the contents of table TA are returned.
Can you tel how the query works ?
Hi Pinal,
I am very regular on your website. I have a query aorund the use of Correlated SubQuery: I want to get the department name which has more than 5 employees in it.
I can achieve this result set using Non-Correlated SubQuery OR Correlated subquery as below:
Simple Group By Clause and Joins:
select d.departmentName from DeptMaster d Inner Join new_Employee1 ne
On d.DeptId=ne.Dept Group by d.departmentName,ne.Dept having COUNT(*) >5
Coorelated Subquery:
select d.departmentName from DeptMaster d where 5 <
(
select COUNT(*) from new_Employee1 ne where ne.Dept=d.DeptId
)
My question is why should I corrleated subquery when I can achieve the same thing without it.
Is there any benefits of writting a correlated sub query.
Please help me to clear my doubt ASAP
Hi,
I have gone thru ur site,its very nice to learn.
Its very helpful to me and it cleared my concept on corelated sub query.
Thanks a lot.:)
hi panel,
can u explain which is better practice to use with our query for Better Permanence because we can do all of it with join also.
Hi Pinal, I read your example of correlated subquery.
Do I need a correlated subquery for the following problem? Is so, what would it be?
Find all customers who have placed a first order between 1/1/2012 and 12/31/2012 and who have ordered a total of at least $250 since 1/1/2013.
Thank you!
Your site is really nice
Hi Pinal,
I read your post about correlated subquery, but i am still confused, how it is working.
Can you please explain that how it executes in database.
I have an Employee table having name and address, and i want to find row number without using ROW_NUMBER(). I used following query:
select name, address, (select COUNT(*) from Employee i where o.name >= i.name) row_num from Employee o
Can You please explain the step by step execution of this query? It will be very helpful for learn correlated subquery.
Thank You!
Hello Sir i want a Not In Select Query in Insert Statement so plz. urgently help me,thanks…
Dear Sir, now i was completed oracle sql developer in chennai. pls let be know what are all the companies requiring oracle sql developer. kindly send me the companies to my mail_id.
Hi Brother, i read your postings most of the time. Really these are very helpful and your explanation makes me clear from the very root of the subject matter. But in this case I have a doubt. As you told Correlated Subquery means the inner query depend upon the outer query. But your example shows it depends upon the table used in the outer query. So what should we say ? The inner query depends upon the outer query or the table used in the outer query….Please clarify
Regards,
Faiyaz
Hi Peenal,
I have seen your blogs, they are really very helpful always.
I need help to get idea, I want to do SQL certifications….
Could you please guide what exactly certifications are and what I should study for them.
please let me know what is the difference between triggers and timestamp
instead of “on update” we can use timestamp , but, why we prefer trigger