Database Related

Difference Between Uncorrelated Subqueries And Correlated Subqueries

Let’s begin out with an example of what exactly an uncorrelated subquery looks like. After this we will compare uncorrelated subquery with a correlated subquery.

Uncorrelated Subquery Illustration

Here is an example of few SQL that exemplifies an uncorrelated subquery:

Select Merchant.Name from Merchant

Where Merchant.ID NOT IN (

Select Requisitions.Merchant_id from Requisitions, Client

Where Requisitions.cust_id = Client.ID

And Client.Name = ‘Samsonic’)

Don’t worry if the above example of the SQL looks scary to you. Still, it is not difficult to understand for our purpose. The subquery part of the above SQL starts after the “NOT IN” statement. The above query is an uncorrelated subquery because the subquery can be run independently of the outer query. Essentially, the subquery has no association with the outer query.

However, a correlated subquery has the opposite property. A correlated subquery can never run independently of the outer query. In the below example of a correlated subquery, you can see the difference yourself:

Example of a correlated subquery

SELECT *

FROM Labor Lab1

WHERE (1) = (

SELECT COUNT(DISTINCT(Lab2.Salary))

FROM Labor Lab2

WHERE Lab2.Salary >Lab1.Salary)

 

In the above correlated subquery you will notice that the inner subquery uses Lab1.Salary, but the alias Lab1 is created in the outer query. Thus, it is known as a correlated subquery, because the subquery indicates a value in its WHERE clause (in the above example, it uses a column belonging to Lab1) that is used in the outer query.

Working of a correlated query

In a correlated subquery, it is significant to understand the sequence of operations. First, a row is processed in the outer query. After this, for that specific row the subquery is executed – thus for each row processed by the outer query, the subquery is also going to be processed.

In the above correlated subquery example, every time a row is processed for Lab1, the subquery will also select that row’s value for Lab1.Salary and run. Afterwards the outer query will move on to the subsequent row, and the subquery is going to execute for that row’s value of Lab1.Salary.

Leave a Reply