Login with  Log in with facebook
Hiring Manager? SIGN UP HERE

what is corelated subquery and how different it is from regular subquery.

Eric Smith
09/07/2011 at 10:26
2 Answers

A correlated subquery uses columns obtained from the main query. It usually needs to be re-evaluated for every row, because the parameters change with every row. For example, this query finds all the students with the highest GPA in each major

SELECT student_number, name, major, gpa
  FROM Students s
    WHERE gpa = (
      SELECT MAX( gpa)
         FROM Students
           WHERE major = s.major)

The inner query has a clause WHERE major = s.major, which is called a correlated subquery, because it must be re-evaluated for every row returned by the outer query.

Michael Zalewski
09/07/2011 at 12:13


Avoid these, try to use joins...

These types of subqueries are normally very inefficient and should be avoided unless table size is small.... always review the SQL Execution plan in such a case and see.

Sudhir Jangir
09/14/2011 at 10:11

If you want to post any answer to this forum then you need to log in.
Schedule a Demo

Schedule a Demo with us

Name *
Email *
Phone *
Company *