Question

The correlated subquery is one of the tricky concepts of SQL. It's similar to recursion in programming which many programmers struggle to understand, but like recursion, it also offers the unique capability to solve many SQL query based problems second highest salary where you need to compare one row of the table to another row. 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 run once and return a value or a set of values which is used by outer query, but 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 the 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. A non-correlated subquery is executed only once and its result can be swapped back for a query, on the other hand, a correlated subquery executed multiple times, precisely once for each row returned by the outer query.

what are good points in this response?

Answer 1

1.The co-related subquery is similar to recursion in programming

2.It is used to solve complex problems like finding second highest salary

3.There is difference between non co-related subquery and co-related subquery ,In co-related subqurey it executes once for every row considered by outer query where as in subquery the inner query executes first and returns the values which are used by outer query

4.Due to repetition of query co-related subquery is slow.

5.we will use corelated subquery when there is no any alternatives due to its slow performance

6.corelated subquery gives precise results.