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