Working with correlated subqueries

A correlated subquery (CS) is a subquery whose outer query results are processed a row at a time, against the subquery result. The subquery result is computed for each row processed. The following are advantages of subqueries:

• Helps in eliminating the need for intermediate or temporary tables. Reduces user temp space and spool.
• Helps in minimizing joining costs.
• If used effectively, is significantly faster than the query using temporary tables.

Now, let's understand the workings of different types of queries in Teradata from the figure:

Ordinary Sub Query: In this case, the inner query is executed only once, and the output of the inner query is used by the outer query. The inner query is not dependent on the outer query:

/*Sub query*/
SELECT
Column1, Column2 FROM 
Table1 
WHERE Column1 IN (SELECT Column1 FROM Table1);

Correlated Sub Query: In this case, the outer query will be executed first, and for every row of the outer query, the inner query will be getting executed. This means the inner query will get executed as many times as the number of rows in the result of the outer query. The outer query output can use the inner query output for comparison. This maintains the dependency between inner and outer queries:

/*Correlated query*/
SELECT Column1, Column2
FROM Table1 Tb1
WHERE Column1 IN 
(
SELECT Column1 FROM Table2 Tb2 WHERE 
Tb1.Column2=Tb2.Column2
);

In this recipe, we will look at the workings of the correlated subquery.