SQL SERVER – Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example

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)

, ,
Previous Post
SQL SERVER – 2005 – Introduction and Explanation to sqlcmd
Next Post
SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

Related Posts

75 Comments. Leave new

  • You are still my hero, thnx.

    Reply
  • 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.

    Reply
  • 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

    Reply
    • Imran Mohammed
      July 10, 2010 5:00 am

      @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

      Reply
  • 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 .

    Reply
  • 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.

    Reply
  • 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 ?

    Reply
  • 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 ?

    Reply
  • Manimaran Duraisamy
    February 3, 2011 11:34 am

    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

    Reply
  • neeraj prasad
    March 8, 2011 3:45 pm

    Dear,

    Result will be same of both of subquery,
    but Noncorrelated subquery Perform faster

    Reply
  • Dear sir,
    your blog is really nice.
    you are the best in explaining the concepts…
    Thank you

    Reply
  • Sir,

    Thanks for this solution . I would like to know how to call web service from stored procedure…please let me know.

    Reply
  • 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)

    Reply
    • 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

      Reply
      • Dinesh Babu
        May 3, 2011 7:21 pm

        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

  • Dinesh Babu
    May 3, 2011 7:22 pm

    Hi All,

    can we have multiple MDF(primary data file) in one database?

    Thanks,
    Dinesh Babu

    Reply
    • Every database can have only one primary file .However you can add as many secondary files as possible

      Reply
  • Hello Sir,
    I have one question how can we use the order by clause in the Sub queries.

    Regards,
    Raghav Sharma

    Reply
  • 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.

    Reply
  • ankur kadiyan
    July 26, 2011 11:53 pm

    respected sir ca n u tell me how to find out the increment in salary (percentage) from the table????

    Reply
  • Can a subquery be used in an insert statement?
    As:
    insert into ______()
    VALUES()

    Is the above valid?

    Reply
  • 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

    Reply
  • 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

    Reply
  • Hi ,
    in SQL can you explain me some security loop holes which can access the program and destroy it?

    Thanks & Regards
    Nikita

    Reply

Leave a Reply

Menu