Subqueries – Subqueries in Oracle SQL

Subject: Information Technology – Database Systems School level: 11th grade – HTL Informatik Prerequisites: DQL – SELECT, WHERE, GROUP BY, Joins Author: HTL Pinkafeld – IF/IT

1. What are subqueries?

1.1 Definition

A Subquery (also: subquery, nested query) is a SELECT statement that is within another SQL statement. The inner query is executed first, its result is then used by the outer query.

SELECT ENAME, SAL
FROM   EMP
WHERE  SAL > (SELECT AVG(SAL) FROM EMP);
--            ──────────────────────────
--                  Subquery (innere Abfrage)
-- ──────────────────────────────────────────
--             Main query (outer query)

1.2 Why subqueries?

Some questions can only be answered elegantly with subqueries:

"Who earns more than average?"
→ Average is only known after reading all lines
→ Subquery calculates average first, main query then filters

"Welche Mitarbeiter arbeiten in derselben Abteilung wie SCOTT?"
→ Department of SCOTT is only known after reading the EMP line
→ Subquery determines the department, main query filters

1.3 Classification

Subquery-Typen nach Ergebnis:
├── Single-Row: returns exactly 1 row → =, <, >, <=, >=, <>
├── Mehrzeilig (Multi-Row):   gibt mehrere Zeilen zurück → IN, NOT IN, ANY, ALL
└── Mehrspaltig (Multi-Col):  gibt mehrere Spalten zurück → (col1, col2) IN (...)

Subquery-Typen nach Position:
├── In WHERE:         häufigste Verwendung
├── In HAVING:        Gruppen mit Aggregaten filtern
├── In FROM:          Inline View (temporäre Tabelle)
├── In SELECT:        skalare Subquery (ein Wert pro Zeile)
└── Correlated: references column of the outer query

1.4 Syntax rules

-- Subquery immer in runde Klammern
WHERE SAL > (SELECT AVG(SAL) FROM EMP)

-- Subquery has own ORDER BY only with FETCH / ROWNUM
-- (einfaches ORDER BY in Subquery ist sinnlos und meist verboten)

-- Subquery kann selbst wieder Subqueries enthalten (Verschachtelung)
WHERE DEPTNO = (SELECT DEPTNO
                FROM   EMP
                WHERE  ENAME = 'SCOTT')

2. Simple subquery in WHERE

2.1 Single-line subquery with =

The most common application: A specific value is determined using a subquery.

-- Who earns exactly as much as SCOTT?
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL = (SELECT SAL
              FROM   EMP
              WHERE  ENAME = 'SCOTT');

-- Who works in the same department as JONES?
SELECT ENAME, DEPTNO
FROM   EMP
WHERE  DEPTNO = (SELECT DEPTNO
                 FROM   EMP
                 WHERE  ENAME = 'JONES')
  AND  ENAME <> 'JONES';

2.2 Single-line subquery with aggregate function

-- Employees who earn more than average
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY SAL DESC;

-- Ergebnis:
-- ENAME   SAL
-- KING   5000
-- FORD   3000
-- SCOTT  3000
-- JONES  2975
-- BLAKE  2850
-- CLARK  2450

-- Earliest employee hired
SELECT ENAME, HIREDATE
FROM   EMP
WHERE  HIREDATE = (SELECT MIN(HIREDATE) FROM EMP);
-- SMITH   17.12.1980

2.3 Single-line subquery with multiple clauses

-- Employees who earn more than the average in their own department
-- (Vorgriff auf korrelierte Subquery – hier mit Subquery im WHERE)

-- First: average salary of the department 20
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20;   -- 2175

-- Then: Employees of department 20 above this value
SELECT ENAME, SAL
FROM   EMP
WHERE  DEPTNO = 20
  AND  SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20);

2.4 Error with single-line subqueries

-- ERROR: Subquery returns multiple rows, = expects exactly one!
SELECT ENAME FROM EMP
WHERE  SAL = (SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
-- ORA-01427: single-row subquery returns more than one row

-- SOLUTION: Use IN instead of = (see Chapter 4)
SELECT ENAME FROM EMP
WHERE  SAL IN (SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

-- ERROR: Subquery does not return a row
SELECT ENAME FROM EMP
WHERE  SAL = (SELECT SAL FROM EMP WHERE ENAME = 'NIEMAND');
-- No error, but no result either (ZERO comparison)

3. Subquery with comparison operators

3.1 All comparison operators

Single-line subqueries can be combined with all comparison operators:

-- Mehr als der am wenigsten verdienende MANAGER
SELECT ENAME, SAL, JOB
FROM   EMP
WHERE  SAL > (SELECT MIN(SAL)
              FROM   EMP
              WHERE  JOB = 'MANAGER');

-- Weniger als der am meisten verdienende CLERK
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL < (SELECT MAX(SAL)
              FROM   EMP
              WHERE  JOB = 'CLERK');

-- Eingestellt nach dem letzten SALESMAN
SELECT ENAME, HIREDATE, JOB
FROM   EMP
WHERE  HIREDATE > (SELECT MAX(HIREDATE)
                   FROM   EMP
                   WHERE  JOB = 'SALESMAN')
ORDER BY HIREDATE;

3.2 Not equal (<>)

-- Employee in a department other than KING
SELECT ENAME, DEPTNO
FROM   EMP
WHERE  DEPTNO <> (SELECT DEPTNO
                  FROM   EMP
                  WHERE  ENAME = 'KING');

4. Multivalued subqueries – IN and NOT IN

4.1 IN with subquery

If the subquery returns multiple rows, IN must be used instead of =:

-- Employees who work in departments located in DALLAS or CHICAGO
SELECT ENAME, DEPTNO
FROM   EMP
WHERE  DEPTNO IN (SELECT DEPTNO
                  FROM   DEPT
                  WHERE  LOC IN ('DALLAS', 'CHICAGO'));

-- Employees who have the same job as someone from Department 10
SELECT ENAME, JOB
FROM   EMP
WHERE  JOB IN (SELECT JOB
               FROM   EMP
               WHERE  DEPTNO = 10)
  AND  DEPTNO <> 10;

4.2 NOT IN with subquery

-- Employees who do NOT have a supervisor from Department 10
SELECT e.ENAME, e.MGR
FROM   EMP e
WHERE  e.MGR NOT IN (SELECT EMPNO
                     FROM   EMP
                     WHERE  DEPTNO = 10);

⚠️ Trap with NOT IN and ZERO:

-- DANGEROUS: If the subquery contains NULL values, NOT IN always returns empty!
SELECT ENAME FROM EMP
WHERE  EMPNO NOT IN (SELECT MGR FROM EMP);
-- MGR contains ZERO (KING has no supervisor)
-- NOT IN mit NULL → kein Ergebnis!

-- SOLUTION: Exclude NULL values ​​in the subquery
SELECT ENAME FROM EMP
WHERE  EMPNO NOT IN (SELECT MGR FROM EMP WHERE MGR IS NOT NULL);

4.3 Multi-column IN comparison

Oracle allows comparing multiple columns at once:

-- Employees with the same job AND salary as an employee from department 30
SELECT ENAME, JOB, SAL
FROM   EMP
WHERE  (JOB, SAL) IN (SELECT JOB, SAL
                      FROM   EMP
                      WHERE  DEPTNO = 30);

5. ANY and ALL operators

5.1 ANY – at least one condition is met

> ANY means: greater than at least one value of the subquery (= greater than the minimum):

-- Mehr verdienen als IRGENDEIN CLERK
SELECT ENAME, SAL, JOB
FROM   EMP
WHERE  SAL > ANY (SELECT SAL
                  FROM   EMP
                  WHERE  JOB = 'CLERK')
  AND  JOB <> 'CLERK';

-- Equivalent to:
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE JOB = 'CLERK')

ANY equivalents:

5.2 ALL – all conditions met

> ALL means: greater than all values ​​of the subquery (= greater than the maximum):

-- Earn more than ALL MANAGERS
SELECT ENAME, SAL, JOB
FROM   EMP
WHERE  SAL > ALL (SELECT SAL
                  FROM   EMP
                  WHERE  JOB = 'MANAGER');

-- Equivalent to:
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'MANAGER')
-- Result: only KING (5000 > 2975)

ALL equivalents:

5.3 Comparison ANY / ALL

-- Gehaltsklasse CLERK: 800, 950, 1100, 1300

-- > ANY: greater than at least one → > 800 (minimum)
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK')

-- > ALL: greater than all → > 1300 (maximum)
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'CLERK')

6. Subquery in HAVING clause

HAVING filters groups – subqueries in HAVING compare aggregate values ​​with calculated reference values:

-- Departments whose average salary is higher than the overall average
SELECT DEPTNO,
       ROUND(AVG(SAL), 2) AS avg_gehalt
FROM   EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP)
ORDER BY avg_gehalt DESC;

-- Ergebnis:
-- DEPTNO  AVG_GEHALT
-- ------  ----------
--     10     2916.67
--     20     2175.00
-- (Abt. 30 mit 1566.67 liegt unter dem Gesamtdurchschnitt von 2073.21)

-- Occupations with more employees than the average for all occupational groups
SELECT JOB, COUNT(*) AS anzahl
FROM   EMP
GROUP BY JOB
HAVING COUNT(*) > (SELECT AVG(cnt)
                   FROM   (SELECT COUNT(*) AS cnt
                           FROM   EMP
                           GROUP BY JOB))
ORDER BY anzahl DESC;

7. Correlated subqueries

7.1 What is a correlated subquery?

With a correlated subquery, the inner query references a column of the outer query. This causes the subquery to be re-executed for each row of the outer query:

-- Employees who earn more than the average in their own department
SELECT e.ENAME, e.DEPTNO, e.SAL
FROM   EMP e
WHERE  e.SAL > (SELECT AVG(i.SAL)
                FROM   EMP i
                WHERE  i.DEPTNO = e.DEPTNO)  -- e.DEPTNO: Reference to external query!
ORDER BY e.DEPTNO, e.SAL DESC;

Execution flow:

Für jede Zeile in EMP (äußere Abfrage):
  1. Lies e.DEPTNO dieser Zeile (z.B. 20)
  2. Führe Subquery aus: SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20
  3. Vergleiche e.SAL mit dem Ergebnis
  4. Zeile ausgeben wenn Bedingung wahr

7.2 Another example: rank in the department

-- Employees who have the highest salary in their department
SELECT e.ENAME, e.DEPTNO, e.SAL
FROM   EMP e
WHERE  e.SAL = (SELECT MAX(i.SAL)
                FROM   EMP i
                WHERE  i.DEPTNO = e.DEPTNO)
ORDER BY e.DEPTNO;

-- Ergebnis:
-- ENAME  DEPTNO  SAL
-- KING       10  5000
-- FORD       20  3000
-- SCOTT      20  3000
-- BLAKE      30  2850

7.3 Correlated Subquery with Aggregate

-- For each employee: Average salary of the department as an additional column
-- (better solved as an inline view – here as a correlated variant)
SELECT e.ENAME,
       e.SAL,
       e.DEPTNO,
       (SELECT ROUND(AVG(i.SAL), 2)
        FROM   EMP i
        WHERE  i.DEPTNO = e.DEPTNO) AS abt_durchschnitt
FROM   EMP e
ORDER BY e.DEPTNO, e.SAL DESC;

8. EXISTS and NOT EXISTS

8.1 EXISTS – Existence check

EXISTS checks whether the subquery returns at least one row. As soon as a row is found, Oracle aborts the search (efficiently!):

-- Departments that have at least one employee
SELECT d.DEPTNO, d.DNAME
FROM   DEPT d
WHERE  EXISTS (SELECT 1
               FROM   EMP e
               WHERE  e.DEPTNO = d.DEPTNO);

-- Result: 10, 20, 30 (Department 40 has no employees)

Convention: In the subquery of EXISTS you often write SELECT 1 or SELECT NULL instead of SELECT * - the specific column values ​​are not important, only whether rows exist.

8.2 NOT EXISTS

-- Departments without employees
SELECT d.DEPTNO, d.DNAME
FROM   DEPT d
WHERE  NOT EXISTS (SELECT 1
                   FROM   EMP e
                   WHERE  e.DEPTNO = d.DEPTNO);

-- Ergebnis: 40   OPERATIONS

8.3 EXISTS vs. IN – the difference

-- Beide liefern dasselbe Ergebnis:
-- Variante 1: IN
SELECT ENAME FROM EMP
WHERE  DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');

-- Variante 2: EXISTS
SELECT e.ENAME FROM EMP e
WHERE  EXISTS (SELECT 1
               FROM   DEPT d
               WHERE  d.DEPTNO = e.DEPTNO
                 AND  d.LOC = 'DALLAS');

When which one?

Rule of thumb: With NOT IN, always consider whether NOT EXISTS is the safer choice - because of the NULL problem.

9. Subquery in FROM clause – Inline View

9.1 What is an Inline View?

A subquery in the FROM clause behaves like a temporary table - it only exists for the duration of the query:

-- Inline View: Average salary per department as a "table"
SELECT e.ENAME,
       e.SAL,
       avg_abt.durchschnitt,
       e.SAL - avg_abt.durchschnitt AS differenz
FROM   EMP e
       JOIN (SELECT DEPTNO,
                    ROUND(AVG(SAL), 2) AS durchschnitt
             FROM   EMP
             GROUP BY DEPTNO)   avg_abt
         ON e.DEPTNO = avg_abt.DEPTNO
ORDER BY e.DEPTNO, differenz DESC;

9.2 Top-N queries with ROWNUM

A classic Oracle application of Inline Views: ROWNUM can only be applied after sorting because ROWNUM is assigned before ORDER BY:

-- WRONG: ROWNUM is assigned before ORDER BY!
SELECT ENAME, SAL
FROM   EMP
WHERE  ROWNUM <= 3
ORDER BY SAL DESC;
-- Gives any 3 lines, then sorted - not the 3 highest!

-- RICHTIG: Zuerst sortieren (Inline View), dann ROWNUM
SELECT ENAME, SAL
FROM   (SELECT ENAME, SAL
        FROM   EMP
        ORDER BY SAL DESC)
WHERE  ROWNUM <= 3;

-- Result: The 3 best paid employees
-- KING   5000
-- FORD   3000
-- SCOTT  3000

-- Top 5 employees by salary with rank
SELECT ROWNUM AS rang, ENAME, SAL
FROM   (SELECT ENAME, SAL
        FROM   EMP
        ORDER BY SAL DESC)
WHERE  ROWNUM <= 5;

-- Ab Oracle 12c: eleganter mit FETCH FIRST
SELECT ENAME, SAL
FROM   EMP
ORDER BY SAL DESC
FETCH FIRST 5 ROWS ONLY;

9.3 Complex Aggregations with Inline View

-- Department with the highest average salary
SELECT *
FROM   (SELECT DEPTNO,
               ROUND(AVG(SAL), 2) AS avg_sal
        FROM   EMP
        GROUP BY DEPTNO
        ORDER BY avg_sal DESC)
WHERE  ROWNUM = 1;

-- Number of employees per salary class and department
SELECT d.DNAME, sub.grade, sub.anzahl
FROM   DEPT d
       JOIN (SELECT e.DEPTNO,
                    s.GRADE,
                    COUNT(*) AS anzahl
             FROM   EMP e
                    JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
             GROUP BY e.DEPTNO, s.GRADE) sub
         ON d.DEPTNO = sub.DEPTNO
ORDER BY d.DNAME, sub.grade;

10. Subquery in the SELECT list

10.1 Scalar Subquery

A scalar subquery in the SELECT list returns exactly one value per row:

-- For each employee: average salary for the entire company as a comparison
SELECT ENAME,
       SAL,
       (SELECT ROUND(AVG(SAL), 0) FROM EMP) AS firma_durchschnitt,
       SAL - (SELECT ROUND(AVG(SAL), 0) FROM EMP) AS differenz
FROM   EMP
ORDER BY differenz DESC;

-- Number of employees in the department as an additional column
SELECT e.ENAME,
       e.DEPTNO,
       e.SAL,
       (SELECT COUNT(*)
        FROM   EMP i
        WHERE  i.DEPTNO = e.DEPTNO) AS kollegen_in_abt
FROM   EMP e
ORDER BY e.DEPTNO, e.ENAME;

10.2 Limitations

-- Scalar subquery MUST return exactly 1 row
-- Multiple lines → ORA-01427
-- No lines → NULL (no error)

-- Skalare Subquery mit mehr als einer Spalte → ORA-00913
SELECT ENAME,
       (SELECT ENAME, SAL FROM EMP WHERE EMPNO = 7839)   -- MISTAKE!
FROM   EMP;

11. Subqueries vs Joins

11.1 When to use what?

Many queries can be solved using both subquery and join:

-- TASK: Name and department name of all DALLAS employees

-- Variante 1: JOIN
SELECT e.ENAME, d.DNAME
FROM   EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO
WHERE  d.LOC = 'DALLAS';

-- Variante 2: Subquery
SELECT ENAME
FROM   EMP
WHERE  DEPTNO IN (SELECT DEPTNO
                  FROM   DEPT
                  WHERE  LOC = 'DALLAS');
-- Disadvantage: DNAME not directly available (only with subquery in SELECT list)

11.2 Decision support

11.3 Performance Notice

-- Subquery is executed once for each row (uncorrelated: once in total)
-- Correlated subquery is executed for EVERY row of the outer query!
-- For large tables: Inline View or JOIN are often more efficient

-- Slow (correlated subquery, n executions):
WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = e.DEPTNO)

-- Faster (Inline View, 1 version):
FROM EMP e JOIN (SELECT DEPTNO, AVG(SAL) AS avg_sal
                 FROM EMP GROUP BY DEPTNO) a
           ON e.DEPTNO = a.DEPTNO
WHERE e.SAL > a.avg_sal

12. Summary and outlook

Overview of all subquery variants

-- Einzeilige Subquery in WHERE (= < > <= >= <>)
WHERE SAL > (SELECT AVG(SAL) FROM EMP)

-- Mehrwertige Subquery in WHERE (IN / NOT IN)
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS')

-- ANY / ALL
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK')
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'MANAGER')

-- Subquery in HAVING
HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP)

-- Korrelierte Subquery
WHERE SAL = (SELECT MAX(SAL) FROM EMP i WHERE i.DEPTNO = e.DEPTNO)

-- EXISTS / NOT EXISTS
WHERE EXISTS     (SELECT 1 FROM EMP e WHERE e.DEPTNO = d.DEPTNO)
WHERE NOT EXISTS (SELECT 1 FROM EMP e WHERE e.DEPTNO = d.DEPTNO)

-- Inline View (Subquery in FROM)
FROM (SELECT DEPTNO, AVG(SAL) AS avg_sal FROM EMP GROUP BY DEPTNO) a

-- Skalare Subquery in SELECT
SELECT ENAME, (SELECT COUNT(*) FROM EMP i WHERE i.DEPTNO = e.DEPTNO)

Checklist

Explain subquery concept and execution order
Einzeilige Subquery mit = und Aggregatfunktionen
Fix bug ORA-01427 (multiple lines instead of one).
Mehrwertige Subquery mit IN / NOT IN
NULL-Falle bei NOT IN kennen und vermeiden
Explain and apply ANY and ALL
Subquery in HAVING
Korrelierte Subquery: Unterschied zu unkorrellierter
EXISTS und NOT EXISTS: Existenzprüfung
Inline View: Correctly solve top-N query with ROWNUM
Skalare Subquery in SELECT-Liste
Subquery vs. Join: wann was?

Typical exam tasks

-- 1. All employees who earn more than BLAKE
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL > (SELECT SAL FROM EMP WHERE ENAME = 'BLAKE')
ORDER BY SAL DESC;

-- 2. Employees with the lowest salary per department
SELECT ENAME, DEPTNO, SAL
FROM   EMP e
WHERE  SAL = (SELECT MIN(SAL) FROM EMP i WHERE i.DEPTNO = e.DEPTNO)
ORDER BY DEPTNO;

-- 3. Departments without employees (two variants)
-- Variante A: NOT IN
SELECT DEPTNO, DNAME FROM DEPT
WHERE  DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL);

-- Variante B: NOT EXISTS
SELECT DEPTNO, DNAME FROM DEPT d
WHERE  NOT EXISTS (SELECT 1 FROM EMP e WHERE e.DEPTNO = d.DEPTNO);

-- 4. The 3 longest employed employees
SELECT ENAME, HIREDATE
FROM   (SELECT ENAME, HIREDATE FROM EMP ORDER BY HIREDATE ASC)
WHERE  ROWNUM <= 3;

-- 5. Employees who earn more than the average in their department
SELECT e.ENAME, e.DEPTNO, e.SAL,
       ROUND(a.avg_sal, 2) AS abt_durchschnitt
FROM   EMP e
       JOIN (SELECT DEPTNO, AVG(SAL) AS avg_sal
             FROM   EMP
             GROUP BY DEPTNO) a ON e.DEPTNO = a.DEPTNO
WHERE  e.SAL > a.avg_sal
ORDER BY e.DEPTNO, e.SAL DESC;

Outlook: Next topics

• DML: INSERT, UPDATE, DELETE, MERGE
• DDL: CREATE TABLE, ALTER TABLE, Constraints
• Views: Virtual tables with CREATE VIEW
• PL/SQL: Procedural extension, loops, conditions, cursors

HTL Pinkafeld – IF/IT | Oracle SQL | 11th grade