What is the difference between Correlated and Un-correlated Sub query?

Rajeev Kumar

1.6k

When we write a subquery in such a way that inner subquery and outer main query are interdependent, then we call it s correlated Sub query. In this case, for executing every row of inner query, the outer query is also executed. The inner query needs data from the outer query for its execution.
select * from tblInvoiceDetail where articleid in (select articleid from tblArticlemaster where doi like ‘RKCbp%’)

In a non-correlated subquery, inner subquery has no dependency on outer query.

A correlated subquery can be thought of as a filter on the table that it refers to, as if the subquery were evaluated on each row of the table in the outer query. An uncorrelated subquery has no such external column references.

Cr Bhargavi

Uncorrelated Subquery:Executes independently of the outer query. Subquery executed once and provides a constant value. Generally faster due to single execution. Example: WHERE price > (SELECT AVG(price) FROM products); Correlated Subquery:Depends on outer query's values. Subquery executed for each row in the outer query. Can be slower due to multiple executions. Example: WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Tasadduq Burney

Uncorrelated Subquery:
An uncorrelated subquery is a subquery that can be executed independently of the outer query. It does not reference any columns from the outer query. The subquery is evaluated only once and its result is used by the outer query. The result of the subquery is typically a single value or a single row. Uncorrelated subqueries are evaluated first, and then their results are used by the outer query.

Correlated Subquery:
A correlated subquery is a subquery that is executed for each row of the outer query. It references one or more columns from the outer query and relies on the values from the outer query to produce results. The subquery is evaluated repeatedly, once for each row of the outer query. The result of the subquery can be a single value or multiple rows. Correlated subqueries are evaluated for each row of the outer query.

Praveen Mishra