Multiple Column Subquery in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Multiple Column Subquery in Oracle with Examples

In this article, I am going to discuss Multiple Column Subquery in Oracle with Examples. Please read our previous article where we discussed Multiple Row Subquery in Oracle with Examples.

What is Multiple Column Subquery in Oracle?

Multiple columns values of the inner query compared with multiple columns values of the outer query is called Multiple Column Subquery in Oracle. The following is the syntax.

SELECT * FROM <TN> WHERE(<COL1>,<COL2>,…………) IN(SELECT <COL1>,<COL2>,……. FROM <TN>);

Example to understand Multiple Column Subquery in Oracle

We are going to use the following Employee table to understand Multiple Column Subquery in Oracle with Examples.

Please use the below SQL Script to create and populate the Employee table with the required sample data.

CREATE TABLE EMPLOYEE
(	
        EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2)
);

INSERT INTO EMPLOYEE VALUES (7369, 'SMITH', 'CLERK',      7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMPLOYEE VALUES (7521, 'WARD', 'SALESMAN',    7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMPLOYEE VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMPLOYEE VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMPLOYEE VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

Example:

SELECT * FROM EMPLOYEE WHERE(JOB, SAL) IN(SELECT JOB, MAX(SAL) FROM EMPLOYEE GROUP BY JOB);
When you execute the above sub query, you will get the following output.

Example:

WAQ to display employee whose JOB, MGR is same as the JOB, MGR of the employee “CLARK”?
Solution:
SELECT * FROM EMPLOYEE WHERE(JOB, MGR) IN(SELECT JOB, MGR FROM EMPLOYEE WHERE ENAME=’CLARK’);
When you execute the above subquery, you will get the following output.

In the next article, I am going to discuss Pseudo Columns in Oracle with examples. Here, in this article, I try to explain Multiple Column Subquery in Oracle with Examples. I hope you enjoy this Oracle Multiple Column Subquery article.