For many people, using noncorrelated subqueries (using In/Not In) seems to be more natural than using Exists/Not Exists and correlated subqueries. But noncorrelated subqueries may lead to some issues, as we presented in our previous post.
In this post, we present how to rewrite noncorrelated subqueries into correlated subqueries in just 3 simple steps.
Step 1 : Review alias
Let’s consider the following query.
In the correlated subquery, we will reference one or more columns of the outer tables in the subquery. In order to do that, such columns must be prefixed with their table’s name or alias.
So, the first step is to be sure we use an alias for each table and that all columns are prefixed with the corresponding table alias. We do not really need to use alias in all tables and to prefix all columns (only in the ones we use in the join), but it is a good practice to prefix the columns with table alias (and also makes the rewrite process more straighfoward).
In the following query, we use aliases (e and d) for tables Employees and Departments. We also use the corresponding prefix in columns Id and Manager_Id .
Step 2 : Create the selection criteria
When using the In operator in noncorrelated subqueryes, we usually compare two columns. The first is of a table from the outer query while the other is from a table of the subquery. So, in our example, the first is the Id column (of Employees) and the other is Manager_Id (of Departments).
As our second step, we create a filter clause in the subquery using this two columns. In the following figure, you can see the added Where e.Id = d.Manager_Id clause.
Step 3 : Replace [Not] In
Our query is ready for the final step, which comprises three changes:
- replace the In (or Not In) by Exists (or Not Exists);
- remove the column that is at the left from the Exists (or Not Exists) operator;
- replace the column from the subquery’s select with a constant (optional). You can also use * (asterisc) instead of a constant, but the use of a constant improves performance in some DBMS’s old editions.
The following image, represents the final query. In this example, we replaced d.manager_id with ‘1’.
We can use all the previous steps for Not In queries, which result in Not Exists queries. In the following query, we present such type of query rewrite.
Now, you can choose to stop using noncorrelated subqueries!