Multiple Row Subquery in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Multiple Row Subquery in Oracle with Examples

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

What is Multiple Row Subquery in Oracle?

When a Subquery returns more than one value is called a Multiple Row Subquery in Oracle. In this Multiple Row Subquery, we can use the operators such as IN, ANY, ALL.

Example to understand Multiple Row Subquery in Oracle

We are going to use the following Employee table to understand Multiple Row Subquery 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);

Example1:

Employee details whose employee job is same as the job of the employee “SMITH”, and “CLARK”?
Solution:
SELECT * FROM EMPLOYEE WHERE JOB IN(SELECT JOB FROM EMPLOYEE WHERE ENAME=’SMITH’ OR ENAME=’CLARK’);
When you execute the above subquery, you will get the following output.

Example2:

Display employee details who are getting min, max salaries?
Solution:

SELECT * FROM EMPLOYEE WHERE SAL IN(
SELECT MIN(SAL) FROM EMPLOYEE
UNION
SELECT MAX(SAL) FROM EMPLOYEE);

When you execute the above query, you will get the following output.

Working with “ANY” and “ALL” Operators in Oracle:

ANY: The ANY operator in Oracle is used to compare any of the values in the given list. For better understanding, please have a look at the following image.

ALL: The ALL operator in Oracle is used to compare all of the values in the given list. For better understanding, please have a look at the following image.

Example3:

Display all employees, whose salary is more than any “SALESMAN” salary?
Solution:
SELECT * FROM EMPLOYEE WHERE SAL>ANY(SELECT SAL FROM EMPLOYEE WHERE JOB=’SALESMAN’);
When you execute the above subquery, you will get the following output.

Example4:

Display all employees, whose salary is more than all “SALESMAN” salaries?
Solution:
SELECT * FROM EMPLOYEE WHERE SAL>ALL(SELECT SAL FROM EMPLOYEE WHERE JOB=’SALESMAN’);
When you execute the above subquery, you will get the following output.

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