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