Teradata Subqueries

Subqueries in Teradata

➠ Subqueries are nested SELECT statement in order to provide output to outer query for data filtering purpose.
    → All subqueries must be enclosed in parentheses.
    → Subqueries can have multiple columns to match with main query.
    → Subqueries will always return unique list of values.

➠ Subqueries can be broadly classified into 2 categories:
Subqueries can be used in following SQL statements
Restrictions for subqueries

Basic subquery
➠ A basic subquery or nested Nested is a subquery that is independent of outer query but provides data to outer query to restrict result of final main query.

Correlated subquery
➠ A correlated subquery is a subquery that uses values from the outer query to restrict result of final main query.
➠ A correlated subquery returns a utmost 1 value for each row of its correlated outer table set.
➠ As this processing is done for each row to filter data therefore it can be inefficient.
➠ Queries with EXISTS / NOT EXISTS clauses will generally have correlated subqueries.