Explain scalar and correlated subqueries in Oracle SQL

A scalar subquery returns exactly one column and at most one row, so it can be used anywhere a single expression is valid — a SELECT-list column, a WHERE comparison. A correlated subquery references a column from the outer query in its own WHERE clause, so it conceptually re-evaluates once per outer row rather than running independently once — the mechanism behind EXISTS-based filtering and per-row scalar lookups.

SELECT
  e.last_name,
  e.salary,
  (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS dept_avg_salary
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM employees e2
  WHERE e2.department_id = e.department_id AND e2.salary > e.salary
);