How to do it...

1. You need to connect to Teradata Database using SQLA or Studio.
2. Let's first collect the statistics of the joining column of both the tables:

/*Collect stats on table*/
Collect stats column EMP_DEPT on EMP_TBL1;
Collect stats column DEPT_ID on EMP_TBL2;

1. Let's execute the following query:

/*Correlated sub query*/
SEL TB1.* FROM EMP_TBL1 TB1
WHERE EXISTS /*Outer Query*/
(SEL TB2.DEPT_ID FROM  /*Inner Query*/
DEPT_TBL2 TB2 
WHERE TB1.EMP_DEPT = TB2.DEPT_ID ) ; 

1. The following would be output of the query:

1. Now let's check the explain plan of the query: