Getting ready

You need to connect to Teradata Database using SQLA or Studio. Now, let's create two tables and insert data into them:

/* Table 1 for outer query*/
CREATE VOLATILE TABLE EMP_TBL1
(
EMP_ID INTEGER ,

EMP_NAME VARCHAR(25),

EMP_DEPT INTEGER

)
primary index(EMP_ID)
on commit preserve rows;

INSERT INTO EMP_TBL1 VALUES (1,'Rajesh',10);

INSERT INTO EMP_TBL1 VALUES (2,'Rob',11);

INSERT INTO EMP_TBL1 VALUES (3,'Joey',10);

INSERT INTO EMP_TBL1 VALUES (4,'Ross','21');

INSERT INTO EMP_TBL1 VALUES (5,'Rose','22');

Now, let's create table 2, which will be used for inner query:

CREATE VOLATILE TABLE DEPT_TBL2
(
DEPT_ID INTEGER,
DEPT_NAME VARCHAR(10),
DEPT_CNTRY VARCHAR(10)
)
PRIMARY INDEX(DEPT_ID, DEPT_CNTRY)
on commit preserve rows;

INSERT INTO DEPT_TBL2 VALUES (10,'IT','IN');
INSERT INTO DEPT_TBL2 VALUES (11,'HR','US');
INSERT INTO DEPT_TBL2 VALUES (21,'MKT','AUS');

Let's first check the relationship between the two tables: