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