Using Subqueries to Solve Queries
Kinds of subqueries
A subquery is a SELECT nested inside another statement. Single-row subqueries return one row and use =, <, >. Multiple-row subqueries return a set and use IN, ANY, ALL, or EXISTS. A correlated subquery references a column from the outer query and is re-evaluated per outer row; an uncorrelated one runs once.
Subqueries may sit in the WHERE clause, the HAVING clause, the FROM clause (an inline view, which must be aliased), or the select list (a scalar subquery, which must return at most one row and one column).
Traps
A single-row operator fed a multi-row subquery raises ORA-01427. NOT IN against a subquery that returns a NULL yields no rows - use NOT EXISTS instead. = ANY is equivalent to IN; > ALL means greater than the maximum; > ANY means greater than the minimum. Inside EXISTS, the select list is irrelevant, so SELECT 1 or * both work. A non-correlated subquery cannot reference outer columns.
Worked example
SELECT last_name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees) AND EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);
The first subquery is scalar and uncorrelated; the second is correlated and acts as an existence test per employee.
Rule of thumb
Ask two questions: how many rows can the subquery return (one vs many), and does it need the outer row (correlated vs not)? Those two answers determine the operator you may use.