Teradata Subquery



Teradata subquery is basically a SELET 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 the some of the features of the subquery.

  • A query can have multiple subquery or subquery can have another subquery.
  • Subqueries does not return duplicate records.
  • Subquery can return individulual vaes or list of values.
  • Subqueries must be enclosed with parenthesis.

 

Teradata Subquery Syntax

Below is the syntax of the subquries.

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 belongs to 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 execute, 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