Multicolumn Subqueries

We can compare elements having multiple values with the set of elements. This is called a multicolumn subquery.

CREATE TABLE EMPDEP(EMPNO NUMBER, DEPTNO NUMBER,SALARY NUMBER);

CREATE TABLE EMPCITY(EMPNO NUMBER, DEPTNO NUMBER,CITY VARCHAR2(30));

INSERT INTO EMPDEP VALUES (1,10,1000);

INSERT INTO EMPDEP VALUES (2,20,2000);

INSERT INTO EMPDEP VALUES (3,30,3000);

INSERT INTO EMPCITY VALUES (1,10,'NAGPUR');

INSERT INTO EMPCITY VALUES (2,20,'DELHI');

INSERT INTO EMPCITY VALUES (3,30,'NAGPUR');

SELECT * FROM EMPDEP

EMPNO DEPTNO SALARY
1 10 1000
2 20 2000
3 30 3000

SELECT * FROM EMPCITY

EMPNO DEPTNO CITY
1 10 NAGPUR
2 20 DELHI
3 30 NAGPUR

SELECT E1.EMPNO, E1.DEPTNO,E1.SALARY
FROM EMPDEP E1 WHERE (E1.EMPNO,E1.DEPTNO)
IN (SELECT E2.EMPNO,E2.DEPTNO FROM EMPCITY E2 WHERE CITY = 'NAGPUR')

EMPNO DEPTNO SALARY
1 10 1000
3 30 3000