KENZO188 GAME ONLINE — Teradata Subquery

June 30, 2023September 25, 2016

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