Difference between Subquery, Nested Subquery and Correlated Subquery

Difference between Subquery, Nested Subquery and Correlated Subquery

Query

Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.

Suppose we have a two tables Student and courses whose structure is given below:-

create table Student (Studentid int identity(1,1), Firstname nvarchar(200), Lastname nvarchar(200),Email nvarchar(100))

create table Course (Courseid int identity(1,1), Coursename nvarchar(250), CourseAdmin int)

Now suppose we insert the following data into these tables:-

For table Student

insert into Student values (‘Atul’,’Bajaj’, ‘atul@abc.com’ )

insert into Student values (‘Vivek’,’Johari’, ‘vivek@abc.com’ )

insert into Student values (‘Ankur’,’Johari’, ‘ankur@abc.com’ )

insert into Student values (‘Tarveen’, ‘Kaur’, ‘Tarveen@abc.com’)

For table Course

Insert into Course values(‘Oracle’,2)

Insert into Course values(‘Automation’,4)

Insert into Course values(‘Java’,2)

Insert into Course values(‘QTP’,4)

Now the query to see all the data from the table student and course is given below:-

Select * from student

Select * from Course

Subquery

If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

For example, if we want to find the name of the course Admin of the course “Oracle”, then the following subquery will be used:-

select Firstname+’ ‘+Lastname from student where studentid in (select courseadminid from course where coursename =’Oracle’)

Result:-

In this example, the sql statement select courseadminid from course where coursename =’Oracle’” is a subquery.

Nested Subquery

If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.

Let us suppose we have another table called “StudentCourse” which contains the information, which student is connected to which Course. The structure of the table is:-

create table StudentCourse( StudentCourseid int identity(1,1), Studentid int, Courseid int)

The Query to insert data into the table “Studentcourse” is

Insert into StudentCourse values(1,3)

Insert into StudentCourse values(2,1)

Insert into StudentCourse values(3,2)

Insert into StudentCourse values(4,4)

Note: – We don’t need to insert data for the column StudentCourseid since it is an identity column.

Now, if we want to get the list of all the student which belong to the course “Oracle”, then the query will be,

select Firstname, lastname from student where studentid in (select studentid from studentcourse where courseid in (select courseid from course where coursename=’Oracle’))

Result:-

In this example we use the nested subquery since the subquery “select courseid from course where coursename=’Oracle’” is itself contained in the another subquery(Parent Subquery) “select studentid from studentcourse where courseid in (select courseid from course where coursename=’Oracle’)”.

Correlated Subquery

If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

Suppose we want to get the details of the Courses (including the name of their course admin) from the Course table, we can use the following query:-

select Coursename ,Courseadminid,(select Firstname+’ ‘+Lastname  from student where studentid=Course.courseadminid)as CourseAdminName from course

Result:-

Here in this example the “select Firstname+’ ‘+Lastname  from student where studentid=Course.courseadminid” is called the correlated subquery since the outcome of this subquery is depends on the column courseadminid of the parent query. This means that the correlated subquery will be executed for each row selected by the parent query.

It is not necessary that the column on which the correlated query is depended is included in the selected columns list of the parent query. For example the   below query will also works even the column courseadminid on which the correlated query is depends , is not included in the selected columns list of the parent query.

select Coursename ,(select Firstname+’ ‘+Lastname  from student where studentid=Course.courseadminid)as CourseAdminName from course

Results:-

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions, Most Imp SQL Concepts, SQL Queries Sub-queries, SQL Server and tagged , , . Bookmark the permalink.

102 Responses to Difference between Subquery, Nested Subquery and Correlated Subquery

  1. Anonymous says:

    Good one Bro.. U made it simple to understand

    Keep Going
    -Winkey

    • Anonymous says:

      Author writes as…."eg of For example, if we want to find the name of the course Admin of the course “Oracle”, then the following subquery will be used:-

      select Firstname+' '+Lastname from student where studentid in (select courseadminid from course where coursename ='Oracle')
      "

      But i say in the above query he is retrieving the name of the student first name + last name and not the coureadmin name.

    • Anonymous says:

      100's of ppl will browse and try to learn something new. So please before posting something on ur blogs pls correct it, veryify it.As already someone pointed out ur insert statement too. What if a newbie trying to learn something out of this blog. Please Ensure before posting anything.

    • admin says:

      Hi Anonymous,
      First of all thanks for visiting my blog and spent time in writing the comments as comments motivate me to do more better. Second if you read my article carefully u got to know that, in my article, I store the course admin details also in the student table. So by the query which u mention as
      select Firstname+' '+Lastname from student where studentid in (select courseadminid from course where coursename ='Oracle')
      gives me the name of the COURSE ADMIN NOT ANY STUDENT. if you run that query and article carefully u will know that very well.

      So next time read blog carefully before writing such comments.
      Keep visiting my blog.:-)

    • admin says:

      Hi Anonymous again,
      Yes, you are right hundreds of people visits my blogs daily and give me valuable comments which motivate me to improve my articles quality better. So before giving wrong comments, reading the articles carefully on not just on my blog but on any blog as UR WRONG COMMENTS WILL GOING TO CONFUSE THE OTHER READERS
      sO Please Ensure before posting ANY COMMENTS THAT YOU READ THE FULL ARTICLES .

      Keep visiting my blog and giving me your valuable comments.

    • Anonymous says:

      hi thanks for responding
      You still did not catch my point… your firstname + lastname gives you the name same as courseadminname but the value is from the studenttable not from the courseadmindetails.. since both are same u are thinking that it is courseadmin name… your courseadmin table is only in your condition and u r not retrieving anything from. please check once again. if possible remove the firstrow in the courseadmindetails and check

  2. Vivek Johari says:

    Thanks Winkey

  3. Anonymous says:

    Thanks mate

  4. Anonymous says:

    hey thanks it was helpful. if u could add certain exapmles for al kind of joins,it would b great…!!

    thanks and cheers…!!

  5. Vivek Johari says:

    Thanks for your response. You can find joins with example on one of my article "Sql Joins- Inner Joins, Self Joins, Outer Joins, Cross Joins"

    http://vivekjohari.blogspot.com/2010/01/sql-joins-inner-joins-self-joins-outer.html

  6. Ajeet kumar says:

    well done dude ..its easy to understand ……..

  7. Vivek Johari says:

    Thanks Ajeet for your response…:-)

  8. Puja singh says:

    Really a good article.

  9. Anonymous says:

    superb sir.The explanation is very easy to understand.

  10. Anonymous says:

    Hi Sir,
    Could you explain me what is generally top n query in oracle.

    Select e1.emp_id from emp e1 where 2 = ( select count(distinct e2.emp_id) from emp_1 e2 where e1.emp_id = e2.emp_id);

    what does here 2 = stands for, i tried with few examples.When 1= given instead of 2 answer changes..could you please explain me..

  11. Anonymous says:

    thank you sir for the wonderful and easy examples. It was very well explained. Thank you once again.

  12. Anonymous says:

    nice explanation. Thanks

  13. Hey Vivek…. Please correct you Insert query.. 🙁
    this your table structure.

    create table Student (Studentid int identity(1,1), Firstname nvarchar(200), Lastname nvarchar(200),Email nvarchar(100))

    And this your insert query..
    insert into Student values ('Atul','Bajaj', 'atul@abc.com' )

    IF you insert like that … Oracle through the error. bcoz you try to insert the varchar value in INT variable. That not right..

    your insert query should like that..
    INSERT INTO Student
    (Firstname, Lastname,Email)
    VALUES
    ('Atul','Bajaj', 'atul@abc.com');

    In that case Oracle insert the value in exact column. 🙂

    • admin says:

      Hi Sumeet,
      Many thanks for your post. This article is written for SQL Server and in SQL Server, this code work fine.:-). However ur suggestion is good, from now on wards, I will try to mention the alternate command for Oracle too.:-)

  14. reddy says:

    Thanks…….,the examples has gave me a clear idea..

  15. Anonymous says:

    nice explanation..

  16. could plz explain about indexed views

  17. Siddharth says:

    Thanks Vivek for making it so simple to understand

  18. sql techi says:

    nice and goood

  19. kindly put datawarehousing questions and answers and put sql quiz and puzzles it will look more precious to see for SQL lovers

  20. rupali says:

    now i understand wt actualy d difference izzz…………. thnkzzz

  21. Manish says:

    Its Perfect…:)

  22. Anonymous says:

    simply superb… gr8 article.
    how do i improve my sql skills? any thoughts ? thanks.

  23. Nice post very helpful

    dbakings

  24. Anonymous says:

    It could have been more elaborate. This just a very basic type of explaination. Examples also are not upto the standard.

  25. Anonymous says:

    After completing so many certifications if your explanation stuff is so weak like you have provided. I doubt your dedication to the technology & authentication of the certification candidate. Very poor it shows improper professional development with years of experience

    • admin says:

      Thanks Anonymous for your comments as its motivate me to improve my article quality. But it is better if you give comments based on my articles not on my certificates as it like that you are more concern about my certificate than my article.
      Also some time it is hard to understand why people hide their identity while criticizing someone as sometimes it put a question mark on the person intention.

      Thanks again
      Vivek

  26. Anonymous says:

    Very useful Vivek ji…. Helped me a lot…..

  27. Anonymous says:

    Hello Vivek, juz wondering if the below query can be alternate query for correlated query example.
    select A.Coursename , B.Firstname+' '+B.Lastname as CourseAdminName from A.course AND B.STUDENT where b.studentid=a.courseadminid

    Ajay.

  28. Mrinal Chakravorty says:

    Good One Vivek..

  29. Anonymous says:

    GOOD Atricle , Thanks for your effort.

  30. Anonymous says:

    In one Of Interview I hve been asked , What is Co-related Query and how it works internally in oracle.
    Also it has been extended wht is diffrenece between co related query and temp table in SQL query.

  31. Anonymous says:

    an excellent example and I really cleared my doubts very clearly…Keep it Guru..

  32. Nisha says:

    Easily understandable, very good article

  33. Anonymous says:

    Hello Sir, you have explained the above aricle very well. Based on the above article I want to ask you that, I want to create a query in which I am comparing a field in two different tables and I want to get the record from the table which is having the higher value after that earlier comparision. I am not able to do this. Waiting for your response.

  34. Sagar Hinge says:

    You made it very simple…
    Thanks..

  35. Anonymous says:

    Hey , Can you please tell me what is difference between sub-query and inline view?

  36. Anonymous says:

    Nice explanations..

  37. Does this come under nested query??
    SELECT Name
    FROM AdventureWorks2008R2.Production.Product
    WHERE ListPrice =
    (SELECT ListPrice
    FROM AdventureWorks2008R2.Production.Product
    WHERE Name = 'Chainring Bolts' );

  38. Pingback: lightweight cotton bathrobes light weave pocketed spa cotton waffle bathrobe womens lightweight long bathrobes . lightweight cotton bathrobes

  39. Pingback: watch strap trondheim special 24mm black leather orange stitching meyhofer width of buckle

  40. Pingback: barato nike air max 97 mujer online qiay068

  41. Pingback: cykelhj盲lm racer lazer tonic gul cykelhj盲lmar racer cykelhj盲lmar

  42. Pingback: pour la victoire havana block heels

  43. Pingback: susie tshirt style jersey jumpsuit. hover to zoom

  44. Pingback: 10 best sneakers made in collaboration with hip hopers

  45. Pingback: primadonna swim california blue legend bikini briefs boxer

  46. Pingback: czarny elegancki kombinezon z wysokim ko艂nierzem czarne

  47. Pingback: nuevo vestido de fiesta de oto帽o para mujer elegante sexy club vestidos formales de boda de

  48. Pingback: detalles de anillo viceroy 30 mujer plata cristal

  49. Pingback: Watch TV Shows

  50. Pingback: Kinokrad 2019 Kinokrad Hd

  51. Pingback: Kinokrad

  52. Pingback: filmy-kinokrad

  53. Pingback: kinokrad-2019

  54. Pingback: filmy-2019-kinokrad

  55. Pingback: serial

  56. Pingback: cerialest.ru

  57. Pingback: dorama hdrezka

  58. Pingback: movies hdrezka

  59. Pingback: HDrezka

  60. Pingback: kinosmotretonline

  61. Pingback: LostFilm HD 720

  62. Pingback: trustedmdstorefy.com

  63. Pingback: bofilm ñåðèàë

  64. Pingback: bofilm

  65. Pingback: 1 seriya

  66. Pingback: Êîíñóëüòàöèÿ ïñèõîëîãà

  67. Pingback: topedstoreusa.com

  68. Pingback: hqcialismht.com

  69. Pingback: viagramdtrustser.com

  70. Pingback: lindamedic.com

  71. Pingback: 4serial.com

  72. Pingback: See-Season-1

  73. Pingback: Evil-Season-1

  74. Pingback: Evil-Season-2

  75. Pingback: Evil-Season-3

  76. Pingback: Evil-Season-4

  77. Pingback: Dollface-Season-1

  78. Pingback: Queer-Eye-We-re-in-Japan-Season-1

  79. Pingback: 2020

  80. Pingback: koma 2020

Leave a Reply