When extracting complex data with SQL, “subqueries” are unavoidable. However, do you struggle with the error “single-row subquery returns more than one row” or get stuck when trying to set conditions on multiple columns at once?
In this article, we will explain the mechanism of Non-Scalar Subqueries in Oracle SQL and implementation techniques for efficiently handling multiple rows and columns. Let’s master everything from the basic IN operator to the professional “Row Value Expression” (multi-column comparison).
- Conclusion / What You Will Learn (To-Do List)
- 1. What is a Non-Scalar Subquery?
- 2. Creating Test Data (Verification Environment)
- 3. Basic: Subqueries Returning Multiple Rows (IN Operator)
- 4. Advanced: Subqueries Returning Multiple Columns (Row Value Expression)
- 5. Inline View: Subqueries in the FROM Clause
- 6. Troubleshooting
- 7. Operations and Performance Considerations
- 8. FAQ
- 9. Summary
Conclusion / What You Will Learn (To-Do List)
A non-scalar subquery is a subquery that returns “multiple rows” or “multiple columns (a table)” instead of 1 row and 1 column (scalar).
- Multi-row comparison: Use the
INoperator, orANY/ALL. - Multi-column comparison: Use the format
(ColA, ColB) IN (SELECT ...)(Row Value Expression). - Use as a table: Treat as an inline view in the
FROMclause or use theWITHclause. - Performance: Consider rewriting to
EXISTSorJOINfor large volumes of data.
1. What is a Non-Scalar Subquery?
First, let’s clarify the terminology and differences in behavior. This is important for understanding the causes of errors and the intent behind queries.
| Type | Return Value Shape | Main Places/Operators Used | Characteristics |
| Scalar Subquery | 1 Row, 1 Column | =, >, <, SELECT clause, etc. | Treated as a single value. Returns an error if multiple rows are returned. |
| Non-Scalar Subquery | Multiple Rows or Multiple Columns | IN, ANY, ALL, FROM clause, EXISTS | Treated as a set (list or table). Suitable for set operations and joins. |
Common Usage Scenarios:
- Finding employees included in a “list of department codes matching specific conditions (e.g., location is DALLAS)” (Multiple Rows).
- Finding employees matching a pair of “maximum salary per department” and its “department number” (Multiple Columns).
2. Creating Test Data (Verification Environment)
Execute the following SQL to create the emp (employee) and dept (department) tables for verification.
Note: If existing tables exist, please delete them or create them with different names.
Prerequisite:
CREATE TABLE and UNLIMITED TABLESPACE (or quota) privileges on the schema are required.
-- 2.1 Create dept table
CREATE TABLE dept (
deptno NUMBER PRIMARY KEY,
dname VARCHAR2(50),
loc VARCHAR2(50)
);
-- 2.2 Create emp table
CREATE TABLE emp (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(50),
job VARCHAR2(50),
mgr NUMBER,
hiredate DATE,
sal NUMBER,
comm NUMBER,
deptno NUMBER,
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)
);
-- 2.3 Insert data
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'YYYY-MM-DD'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'YYYY-MM-DD'), 2975, NULL, 20);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'YYYY-MM-DD'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09', 'YYYY-MM-DD'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1987-07-13', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1981-11-17', 'YYYY-MM-DD'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'YYYY-MM-DD'), 1500, 0, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 3000, NULL, 20);
COMMIT;
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------------- --------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 13-JUL-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
10 rows selected.
3. Basic: Subqueries Returning Multiple Rows (IN Operator)
This is the most typical pattern of non-scalar subqueries. It determines if the value of the target column is included in the “list of values” returned by the subquery.
Employees belonging to departments where the location is ‘DALLAS’
With the = operator, an error occurs if the subquery returns multiple rows (e.g., if there are multiple departments in DALLAS). However, IN can handle this safely.
SELECT ename, job, deptno
FROM emp
WHERE deptno IN (
SELECT deptno
FROM dept
WHERE loc = 'DALLAS'
);
Execution Result:
SQL> SELECT ename, job, deptno
2 FROM emp
3 WHERE deptno IN (
4 SELECT deptno
5 FROM dept
6 WHERE loc = 'DALLAS'
7 );
ENAME JOB DEPTNO
---------- ---------- ----------
SMITH CLERK 20
JONES MANAGER 20
SCOTT ANALYST 20
FORD ANALYST 20
Point: If
NULLis included in theINlist, usingNOT INwill result in zero rows returned (becomes UNKNOWN). It is necessary to take measures such as addingWHERE col IS NOT NULLon the subquery side.
4. Advanced: Subqueries Returning Multiple Columns (Row Value Expression)
This is one of the powerful features of Oracle SQL. It compares multiple columns as a pair. It is extremely effective when searching for rows that satisfy “Department Number” and “Conditions for that Department” simultaneously.
Employees receiving the “Maximum Salary” for each department
Calculate the maximum salary for each department and extract employees that match that combination of “Department Number” and “Salary Amount”.
SELECT e.ename, e.sal, e.deptno
FROM emp e
WHERE (e.deptno, e.sal) IN (
-- Returning 2 columns: "Department" and "Max Salary"
SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno
);
Explanation:
- The subquery performs
GROUP BY deptnoto calculate the maximum salary for each department (e.g., Dept 10 is 5000, Dept 20 is 3000…). - The main query’s
WHERE (e.deptno, e.sal) IN ...determines if the employee’s own department and salary exactly match that pair.
Execution Result:
SQL> SELECT e.ename, e.sal, e.deptno
2 FROM emp e
3 WHERE (e.deptno, e.sal) IN (
4 SELECT deptno, MAX(sal)
5 FROM emp
6 GROUP BY deptno
7 );
ENAME SAL DEPTNO
---------- ---------- ----------
BLAKE 2850 30
SCOTT 3000 20
KING 5000 10
FORD 3000 20
5. Inline View: Subqueries in the FROM Clause
This method treats the result of a subquery as a “temporary table” and joins it in the main query. This is also a usage of non-scalar (table format) subqueries.
When using JOIN
By using JOIN instead of IN, you can display columns from the subquery side (in this case, dname) in the results.
SELECT e.ename, e.job, d.dname
FROM emp e
JOIN (
-- Subquery functioning as a temporary view
SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS'
) d
ON e.deptno = d.deptno;
SQL> SELECT e.ename, e.job, d.dname
2 FROM emp e
3 JOIN (
4 SELECT deptno, dname
5 FROM dept
6 WHERE loc = 'DALLAS'
7 ) d
8 ON e.deptno = d.deptno;
ENAME JOB DNAME
---------- ---------- --------------------
SMITH CLERK RESEARCH
JONES MANAGER RESEARCH
SCOTT ANALYST RESEARCH
FORD ANALYST RESEARCH
When using the WITH clause (Common Table Expression)
To improve readability, separating the subq5uery using the WITH clause is a modern best practice.
WITH dallas_depts AS (
SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS'
)
SELECT e.ename, e.job, d.dname
FROM emp e
JOIN dallas_depts d
ON e.deptno = d.deptno;
SQL> WITH dallas_depts AS (
2 SELECT deptno, dname
3 FROM dept
4 WHERE loc = 'DALLAS'
5 )
6 SELECT e.ename, e.job, d.dname
7 FROM emp e
8 JOIN dallas_depts d
9 ON e.deptno = d.deptno;
ENAME JOB DNAME
---------- ---------- --------------------
SMITH CLERK RESEARCH
JONES MANAGER RESEARCH
SCOTT ANALYST RESEARCH
FORD ANALYST RESEARCH
6. Troubleshooting
Here are representative errors related to non-scalar subqueries and how to deal with them.
| ORA Error | Cause | Solution |
| ORA-01427 single-row subquery returns more than one row | The subquery is unexpectedly returning multiple rows. | 1. Change = to IN.2. Add ROWNUM = 1 or MAX() etc. to the subquery to narrow it down to 1 row. |
| ORA-00913 too many values | The number of columns being compared does not match the number of columns in the subquery, as in WHERE (A, B) IN (SELECT X, Y, Z ...). | Match the number of columns. |
7. Operations and Performance Considerations
Differentiation from EXISTS
When handling large volumes of data, EXISTS (correlated subquery) may perform better than IN. This is because EXISTS stops scanning as soon as one matching row is found.
Rewrite example using EXISTS:
SELECT ename, job
FROM emp e
WHERE EXISTS (
SELECT 1
FROM dept d
WHERE d.deptno = e.deptno
AND d.loc = 'DALLAS'
);
SQL> SELECT ename, job
2 FROM emp e
3 WHERE EXISTS (
4 SELECT 1
5 FROM dept d
6 WHERE d.deptno = e.deptno
7 AND d.loc = 'DALLAS'
8 );
ENAME JOB
---------- ----------
SMITH CLERK
JONES MANAGER
SCOTT ANALYST
FORD ANALYST
Leveraging Indexes
By creating indexes on columns that serve as join conditions for subqueries (such as deptno) or filter conditions (loc), you can avoid a FULL TABLE SCAN and expect faster performance.
-- Create as needed
-- CREATE INDEX idx_dept_loc ON dept(loc);
8. FAQ
Q1: Can I use > or < with non-scalar subqueries?
A1: You cannot use them as is. The right side of a comparison operator must be a single value. However, you can do this by adding modifiers like > ALL (greater than everything) or > ANY (greater than any one).
Q2: Should I use a Subquery or a JOIN?
A2: If you want to display columns from the subquery side (e.g., Department Name) in the result, a JOIN is mandatory. If the purpose is only filtering (narrowing down), IN or EXISTS might convey the intent better, depending on readability and the optimizer’s decision.
Q3: What happens if NULL is included?
A3: When using NOT IN, if the result of the subquery contains even one NULL, the result will be completely empty. This is due to SQL’s three-valued logic. When using NOT IN, always ensure you add WHERE col IS NOT NULL on the subquery side.
9. Summary
Non-scalar subqueries are essential techniques for flexibly handling data with multiple rows and multiple columns.
- Use
INfor simple list comparisons, and(ColA, ColB) INfor composite conditions. - If you want to handle the subquery results as a table, utilize the
FROMclause orWITHclause. - If you encounter error ORA-01427, check if the subquery is returning multiple rows.
We recommend using the provided dataset to actually verify the differences in execution plans between IN and EXISTS.
This article explains concepts based on Oracle Database 19c (screens and defaults may differ in other versions).
[reference]
Oracle Database SQL Language Reference, 19c
コメント