Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Correlated vs. Uncorrelated Subqueries – Querychat

Subqueries can be categorized as correlated subqueries and uncorrelated subqueries, based on how the inner query is parsed. This article discusses these two types of subqueries, including performance considerations for correlated subqueries.

Let’s discuss each of them in more detail. It might be a bit confusing at first on the theoretical level, but once we see the examples, we will understand the difference between these two. 

SQL uncorrelated subquery

In an uncorrelated subquery, the inner query is logically evaluated only once. This type of subquery is also called self-contained subquery or simple subquery.

Before we take a look at the examples, suppose we have two tables named Project and ProjectType, with the structure and data described in Figure 1. All examples provided in this article use these two tables.

Figure 1. Project and ProjectType tables

Now, let’s look at an example.

Example 1. Uncorrelated subquery

The following query gets the name and budget of projects with fixed-price type. Note that the inner query in the SQL statement below begins after the “=” operator.

SELECT Name, Budget
FROM Project 
WHERE ProjectTypeId = (
SELECT Id FROM ProjectType WHERE Type = 'Fixed-price'
)

Result:

Name Budget
Write SEO articles 500.00
Publish articles online 100.00

Table 1. Result of the uncorrelated subquery example

The inner query which returns the Id of the ProjectType equals Fixed-price is logically evaluated first (it returns 2). After that, the outer query can be represented with the following equivalent query: 

SELECT Id, Name
FROM Project 
WHERE ProjectTypeId = 2

As we can see in this example, basically, the inner query can be run independently, and its value does not depend on the outer query. This is the reason why this subquery called self-contained or uncorrelated subquery.

SQL correlated subquery

A correlated subquery is different from a correlated one in that its inner query depends on the values provided by the outer query. Therefore, we cannot run the inner query independently. 

In a correlated subquery, the inner query is evaluated each time the database retrieves a new row from the outer query. This type of subquery is also known as a synchronized subquery.  

Example 2. Correlated subquery

The following query gets the name and budget of projects with an hourly type. Notice that we put the project type (‘Hourly’) as a constant in the outer query.

SELECT Name, Budget
FROM Project
WHERE 'Hourly' = (
SELECT Type
FROM ProjectType
WHERE Id = ProjectTypeId
)

Result:

Name Budget
Analyze keywords 80.00

Table 2. Result of the correlated subquery SQL example

The inner query must be logically evaluated several times because it contains the ProjectTypeId column, which belongs to the Project table in the outer query. And, the value of the ProjectTypeId column changes every time the database engine examines a different row of the Project table in the outer query. 

Let’s take a look at how the database might process the query. 

  • First, the system retrieves the first row of the Project table for the outer query. It compares the ProjectTypeId of that row, which is 1, with values of the Id column from the ProjectType table in the inner query. The inner query returns the value ‘Hourly’, which is equal to the constant value ‘Hourly’ in the outer query. So the outer query’s condition (WHERE ‘Hourly’ = …) is met, and the outer query returns this first row from the Project table. 
  • Then, the system retrieves the next row of the Project table and repeats the comparison of project type id  in both tables. The second Project has ProjectTypeId value equals 2, and thus, the result set of the inner query is ‘Fixed-price’. This value is not equal to constant value ‘Hourly’, so the condition is not met, and the second row (Write SEO articles) is not displayed in the result.

The same process is applied to the third row of the Project table. Finally, the result set has only one row. 

Performance consideration

In correlated subqueries, the inner query needs to be evaluated for each row proceed by the outer query. Thus, the performance can be slow if the number of rows in both tables is large. 

When this happens, we may consider reforming these subqueries to use JOIN or applying indexes to database tables. If there are significant performance differences between these solutions, choose the optimal one.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc