Subquery vs Correlated Subquery in SQL

In this article, we will learn what is the difference between two queries the major difference is the order of the query and the relation between the queries.

What is Subquery?

  • In  this query the two part outer part query and the inner part query the outer part query is executed as a parent query and the inner part query is executed as independent. first executed inner part query and the result set is send to the parent outer query.

What is Correlated Subquery?

  • In this query as also the two part outer and inner part of the query and they are linked and dependent to each other to generated the final result of your query.

Examples:

Subquery

select Name, Email, ContactNo from CandidateMasterEntities where Id in(select CandidateId from CandidateSkillEntities)

Correlated Subquery

select CME.Name, CME.Email, CME.ContactNo from CandidateMasterEntities as CME where Id in(select CandidateId from CandidateSkillEntities as CSE where CSE.CandidateId = CME.Id)

How it is Working:

Subquery

  • In the beginning, it chooses every entry in the CandidateSkillEntities table, returning every CandidateSkills in the table.
  • The outer query then uses the IN operator to retrieve all the Candidate table records with an CandidateId in the result set produced by the inner query, returning just the records that match.
  • The output that we can see above is the outcome.

Correlated Subquery

  • The outer query first runs and chooses the first Candidate record from the CandidateMasterEntitie table.
  • In this instance, Mark’s CandidateId is 1. The inner query uses this record as input and looks to see whether any records in the CandidateSkillEntities database have an CandidateId that matches the Id of the record Mark that was obtained from the outer query.
  • Since it satisfies the where condition in the outer query, this record is maintained for the result set if it finds any matches in the CandidateSkillEntities table.
  • otherwise, the outer query obtains the next record. Once more,
  • the second record is supplied as an input to the inner query, and this process is repeated until all of the CandidateMasterEntities table’s records have been sent.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories