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' )
|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 )
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.
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.