Teradata Subqueries

Pradeep

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:

Basic / Nested subquery
Correlated subquery

Subqueries can be used in following SQL statements

SELECT Statements to filter required rows.
DELETE Statements to delete rows as returned by subquery output.
UPDATE Statements.
View Definitions to restrict data.
Table creation to restrict limited set of data in new table.
Subqueries support qualifiers like ALL, ANY, SOME, LIKE, NOT LIKE for outer query.
Subqueries can be objects of an IN, NOT IN, EXISTS and NOT EXISTS clause.

Restrictions for subqueries

Subqueries can be nested up to a depth of 64(maximun) else it will fail with below error.
```
*** Failure 3995 Maximum number of subquery nesting levels has been exceeded.
```

TOP n option cannot be used in subqueries else it will fail with below error.


*** Failure 6916 TOP N Syntax error: Top N option is not supported in subquery.

Sample clause cannot be used in subqueries else it will fail with below error.


*** Failure 3706 Syntax error: SAMPLE clause is not allowed in subqueries.

ORDER BY clause cannot be used in subqueries else it will fail with below error.
```
*** Failure 3706 Syntax error: ORDER BY is not allowed in 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.

➩ Example 1: is a example of subquery.
```
SELECT * FROM table1 
WHERE id IN 
      ( SELECT 
           id 
        FROM 
           table2 
      );
```
Explanation:
- In the above example, subquery 'SELECT id from table2' will return all the 'ids' present in table2.
- These 'ids' from subquery will be used to filter data in table1 using IN clause.
- Above query will return only those records from table1 where 'id' value is also present in table2.
➩ Example 2:
```
SELECT * FROM table1 
WHERE (id,transaction_dt) NOT IN 
      ( SELECT 
           id,
           transaction_dt
        FROM 
           table2 
      );
```
Explanation:
- In the above example, subquery 'SELECT id,transaction_dt from table2' will return all the 'ids & transaction_dt' pairs present in table2.
- These 'ids & transaction_dt' pairs from subquery will be used to filter data in table1 using NOT IN clause.
- Above query will return only those records from table1 where 'id & transaction_dt' pair values are also present in table2.

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.

➩ Example 1:
```
SELECT * FROM table1 
WHERE EXISTS 
      ( SELECT 
           1 
        FROM 
           table2 
        WHERE table1.id=table2.id
      );
```
Explanation:
- In the above example, 'table1.id=table2.id' equality is setting correlation between subquery table and main query table.
- Above query will return only those records from table1 where 'id' value is also present in table2.
➩ Example 2:
```
SELECT * FROM table1 
WHERE NOT EXISTS 
      ( SELECT 
           1 
        FROM 
           table2 
        WHERE table1.id=table2.id
      );
```
Explanation:
- In the above example, 'table1.id=table2.id' equality is setting correlation between subquery table and main query table.
- Above query will return only those records from table1 where 'id' value is not present in table2.