What is the difference between correlated subquery and nested subquery

Kumaran Kumaran 65 1 1 silver badge 4 4 bronze badges. Saravanakumar Kandasamy Saravanakumar Kandasamy 51 1 1 bronze badge. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Does ES6 make JavaScript frameworks obsolete? Podcast Do polyglots have an edge when it comes to mastering programming Featured on Meta. Now live: A fully responsive profile. Linked 0. Related Hot Network Questions.

Question feed. Stack Overflow works best with JavaScript enabled. If the output of Inner query count is more than 1 then these subqueries are called as multi row subqueries. Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query. Correlated query is the query which is executed after the outer query is executed. The outer query is always dependent on inner query.

The approach of the correlated subquery is bit different than normal subqueries. In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.

The result of correlated subquery determines whether the fetched row should be the part of our output results. The Subqueries are called as nested subqueries when another subquery is used in where or having condition of the Outer Query.

Skip to content. Change Language. Related Articles. Table of Contents. Save Article. Improve Article. It gives you a different kind of power. The main difference between a regular, non-correlated, and correlated subquery in SQL is in their working, a regular subquery just runs once and returns a value or a set of values that is used by the outer query. While correlated subquery runs for each row returned by the outer query because the output of the whole query is based upon comparing the data returned by one row to all other rows of the table.

That's why it is also very slow and generally avoided until you don't know any other way to solve the problem. One of the most popular examples of the correlated subquery is about finding the second highest salary of an employee in a given table.