Teradata Subquery

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

Department table

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