Database Related

What Is The Difference Between Correlated Subqueries And Uncorrelated Subqueries?

The meaning of a subquery can be understood from its name – it’s a query within a query. Subquery is also known as a nested query and is used to restrict the search results after they have been filtered from the main query. Subqueries are used with UPDATE, SELECT, DELETE and INSERT statements. Statements of Subqueries written can either be correlated or uncorrelated. We will see how they’re written, how do they work, and eventually, what is the difference between them.

Correlated Subquery:

When a subquery contains a reference to another object that is defined in the parent statement, it is called an outer reference. Every subquery that consists of an outer reference is a correlated subquery. These subqueries can never be interpreted without the outer query. This is because the subquery will always use a reference or a result from parent row to evaluate the final output.

How Does It Work?

The parent statement executes first and then each row from the outer reference is checked with the subquery to obtain the required results. For example:

SELECT Name

FROM Products

WHERE Sales > (

SELECT AVG (ItemsSolds)

FROM Orders

WHERE Products.ID = Orders.ProductID );

The above example is the common syntax for a simple correlated subquery. The Products.ID reference in the subquery above is the outer reference. The purpose of this query is to extract the Name of all the products whose Sales have exceeded the average number of items sold for the product. The important thing to note here is that the product is being tested by the WHERE clause of the main query however, the criteria for the WHERE clause is determined by the subquery. The last WHERE clause of the subquery accesses a table (Products), which is not referenced in the subquery but the main query instead.
This shows the reference that is used between the subquery and the parent query hence making it a correlated subquery.

Uncorrelated Subquery:

An uncorrelated subquery is one in which there is no reference to the parent statement from within the subquery. There is no outer reference in an uncorrelated subquery which is what makes it different from the correlated subquery. The results of an uncorrelated subquery can be interpreted without the outer query (this means that the subquery is independent of the parent query). Here is an example of an uncorrelated subquery that grabs the average items sold from the Orders table and returns the value to the outer query for further evaluation:

SELECT Name

FROM Products

WHERE Sales > (

SELECT AVG(ItemsSold)

FROM Orders );

When this uncorrelated subquery is executed, the database server will only compute the value once as opposed to several times (the case with correlated queries).

Leave a Reply