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 (http://blog.SQLAuthority.com)

About these ads

75 thoughts on “SQL SERVER – Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example

  1. 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.

    Thanks,
    T.Manikumar

  2. I am kaviarasu.I am learning Oracle DBA and i am going to write the certification.Could you sed me the Oracle SQL questions to the above e-mail id.

  3. Hello Dave,

    Your site is really nice.can u send me those queries which generally asked in Interviews.I really need it.and more question What is the difference between rowLock and NO Lock.

    Thanks and Regards
    Aavesh Agarwal

  4. Hi Aavesh,

    rowlock is Record wise lock on data.
    NoLock is for Object which will not lock any records & whick is not applicable to DML commands like insert, update & Delete commands

    Thanks,
    Chanti

  5. As per books available in the market,it says
    DELETE table can be ROLLBACK while
    TRUNCATE table CANNOT be ROLLBACK,
    But it is paractically wrong,Both can be roll back…
    Then why this is so……………

      • Using both DELETE and TRUNCATE commands can be rolled back.
        By default SQL Server commits transactions disabling Rollback.
        There are two approaches to rollback your records in case of any mishap or accidently having a record(s)) deleted.

        Approach 1:
        BEGIN TRANSACTION
        …your statements…
        COMMIT

        Approach 2: SET IMPLICIT_TRANSACTIONS ON

        Try this as a test:

        Step 1: Approach 2

        Step 2: Assuming you have a table ‘tblTableName’ containing rows.

        Step 3:
        1. DELETE FROM where Code = ”
        ROLLBACK
        Note: You will see deleted records back into your table.

        2. TRUNACATE TABLE
        ROLLBACK
        Note: You will see deleted records back into your table. Unless you apply COMMIT after each statement that will not then roll back.

        Major Difference to remember:
        DELETE (DML command): Acts on single or multiple rows. Does not deallocate data page space that contains data.

        TRUNCATE (DDL command): Acts on table object, locks it and then purges all rows. Allocates data page space. This is for use only when you need to empty the whole table.

        Hope this difference helps.

      • Just a correction with statement, I used redirectional operators that got omitted. :-D

        Here is my test example;

        Step 3:
        1. DELETE FROM tblEmpMaster where Code = ’01′
        ROLLBACK
        Note: You will see deleted records back into your table.

        2. TRUNCATE TABLE tblEmpMaster
        ROLLBACK

  6. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 2 Journey to SQL Authority with Pinal Dave

  7. hello pinal,
    Wish you all happiness. I too read your articles, It is very good.

    pinal please give me the solution of this question.

    suppose we have a table and two columns ……..
    Now the question is we have to interchange the values of
    this columns using single query.

    please suggest the solution ,

    thanks and regards
    pawan

  8. Actually if you can post something on order of joins to be followed when working with ..say 5 to 10 tables, it will be great. as the joins increase then how should be order of table to be appearing in join.

    • I’ve understood that you make the most limiting joins first. Then proceed with all the others.

      But simply test it! Write your query in different ways and measure how long they take. Check also the Execution Plan in order to find out if you need to add indexes.

      But I must confess that I don’t always practice what I preach. I’m currently dealing with queries where I have to make many OUTER and INNER JOINs, many subqueries, groupings and calculations. I just write good enough query and if it’s too slow it is caught in the tests and it’s sent back to me, and then I try to optimize it.

      I’m getting lazy :(

  9. Hi Dave,

    I need to have something like this:

    SELECT
    t1.col_x,
    t1.col_y,
    (SELECT value FROM t1 WHERE … AND desc = ’12h’) AS ‘ 24h’
    FROM (



    ) AS t1 RIGHT JOIN ( … ) AS t2 ON( .. = .. )

    This returns me the error:
    Invalid object name ‘t1′.

    I saw that to use a subquery I can’t use Temporary Tables. Is there any way to achieve this result? I’m using SQL2005, and I find out that I could use ‘PIVOT and UNPIVOT’ but I haven’t tried yet.

    Thanks in advance,
    Nuno Costa

  10. hiiiiiiii ,
    sir /madam i saw yr website . It’s super & very nice …………….i m doing Sql Server 2005 ……….i have some doubts if u send me some tips about sql server 2005 in my e mail id ……….i will be very happy………..plz sir help me ………
    thanks……….
    regards
    amar

    • having the knowledge of stored procedure,index,sqbquries,
      cursor,view and trigger are sufficent for the student level.

      Questions :
      finding 2 highest salary
      using top variable and row_number functions

      inline quries

      dynamic quries etc.

  11. Hi,

    Could u pls tell us at what scenario we have to use Correlated Subquery? Cos as you told, we can replace this Correlated Subquery by using In, Join clauses!

    I meant, what is the need and advantages of this?

    Thanks,
    Sam.

  12. 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.

  13. 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

  14. 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 .

  15. 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.

  16. 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 ?

  17. 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 ?

  18. 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

  19. Sir,

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

  20. 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

  21. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31 Journey to SQLAuthority

  22. 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.

  23. 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

  24. 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

  25. 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

  26. 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?

  27. 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

  28. 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

  29. 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 ?

  30. 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

  31. 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.:)

  32. 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.

  33. 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!

  34. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

  35. 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!

  36. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s