Teradata subquery is basically a SELECT query within a query. The SELECT query called as inner query executed first and the outer query uses the result from the subquery.
Below are some of the features of the subquery.
- A query can have multiple subqueries or a subquery can have another subquery.
- Subqueries do not return duplicate records.
- A subquery can return individual values or a list of values.
- Subqueries must be enclosed with parentheses.
Teradata Subquery Syntax
Below is the syntax of the subqueries.
SELECT col1, col2, col3,… FROM Outer Table WHERE col1 OPERATOR ( Inner SELECT Query);
Subquery Example
Consider the below Student and Department table.
Student table
| Roll_No | First_Name | Last_Name | City | dept_no |
| 101 | Akash | Sharma | Mumbai | 204 |
| 106 | Sucheta | Mandal | Delhi | 201 |
| 104 | Raju | Saha | Mumbai | 204 |
| 102 | Sagar | Sharma | Kolkata | 205 |
| 100 | Robert | PY | Mumbai | 205 |
| 107 | Sourav | Das | Kanpur | 204 |
| 105 | Avijit | Pal | Bangalore | 201 |
| 103 | Milon | Mukherjee | Kolkata | 205 |
Department table
| dept_no | dept_name |
| 205 | EEE |
| 201 | CSE |
| 204 | ECE |
The following query identifies the Student who belongs to the CSE department. The inner SELECT returns the dept_no of the CSE department and the outer query uses that result to identify the student details.
SELECT ROLL_NO, FIRST_NAME, LAST_NAME FROM TERADATAPOINT.STUDENT WHERE DEPT_NO = (SELECT DEPT_NO FROM TERADATAPOINT.DEPARTMENT WHERE DEPT_NAME='CSE' );
When the above query executes, it produces the below result.
*** Query completed. 2 rows found. 3 columns returned. *** Total elapsed time was 1 second. Roll_No First_Name Last_Name ----------- ---------------- --------------- 106 Sucheta Mandal 105 Avijit Pal
The following query identifies the student belongs to ECE and EEE departments. The inner SELECT returns the dept_no of the ECE and EEE departments and the outer query uses that result to identify the student details.
SELECT ROLL_NO, FIRST_NAME, LAST_NAME
FROM TERADATAPOINT.STUDENT
WHERE DEPT_NO IN (SELECT DEPT_NO FROM TERADATAPOINT.DEPARTMENT
WHERE DEPT_NAME IN ('ECE','EEE')
);
When the above query execute, it produces the below result.
*** Query completed. 6 rows found. 3 columns returned. *** Total elapsed time was 1 second. Roll_No First_Name Last_Name ----------- -------------------------------------------------- ------------ 100 Robert PY 103 Milon Mukherjee 102 Sagar Sharma 107 Sourav Das 104 Raju Saha 101 Akash Sharma