Subqueries in Oracle SQL—scalar, multi-row, and correlated—enhance query flexibility by enabling modular logic, dynamic data handling, and complex filtering. Scalar subqueries return a single value and are ideal for comparisons or expressions such as computing the average salary; 1. they simplify thresholds without hardcoding values. Multi-row subqueries yield multiple results and work with operators like IN, ANY, or ALL to dynamically filter against changing datasets; 2. they support adaptable queries by avoiding hardcoded lists. Correlated subqueries depend on the outer query, enabling context-aware row-by-row evaluation such as comparing individual salaries to department averages; 3. they allow precise filtering but may impact performance if used excessively. Together, these subquery types streamline complex logic into readable, maintainable SQL while emphasizing efficient data responsiveness when applied appropriately.

Subqueries—especially scalar, multi-row, and correlated types—add a lot of flexibility to Oracle SQL. They let you break complex problems into smaller pieces, reuse logic, and write more dynamic queries without relying on multiple separate statements.

Scalar Subqueries: Get a Single Value When You Need It

A scalar subquery returns exactly one value (one row, one column). This makes them perfect when you need to plug in a single result into an expression or comparison.

For example, say you want to find employees who earn more than the average salary:

SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Here, the subquery calculates the average salary once and uses it as a threshold. The benefit? You don’t have to hardcode a number that might change later.

You can also use scalar subqueries in SELECT clauses. Like this:

SELECT last_name, salary, (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Just be careful—if your scalar subquery returns more than one row, Oracle will throw an error. So always make sure it's truly returning a single value.

Multi-Row Subqueries: Compare Against Multiple Results

Multi-row subqueries return more than one row (but still one column). These are super useful when you want to filter based on a list of values that come from another query.

A common use case is with operators like IN, ANY, or ALL. For instance, to find employees working in departments that have locations in region 1:

SELECT last_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id IN (
        SELECT location_id
        FROM locations
        WHERE region_id = 1
    )
);

This kind of nesting helps avoid hardcoding department IDs and keeps your query adaptable to data changes.

One thing to watch: if you try using = instead of IN, you’ll get an error because it expects a single value. So match the operator to the type of subquery you're running.

Correlated Subqueries: Connect Inner and Outer Queries Dynamically

Correlated subqueries depend on the outer query for their values—they’re not self-contained. That means they run once for each row processed by the outer query, which can make them slower but very powerful when used right.

Say you want to find employees whose salary is higher than the average salary in their own department:

SELECT e.last_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Here, the inner subquery uses e.department_id from the outer query. That’s what makes it correlated—it dynamically adjusts based on the current employee being evaluated.

• These are great for row-by-row comparisons.
• But they can be performance-heavy, especially over large datasets.
• If possible, consider rewriting them as joins for better efficiency.

Still, sometimes there's no better way to express certain logic clearly.

Subqueries open up a lot of doors in Oracle SQL. Scalar ones simplify comparisons, multi-row lets you work with sets of data dynamically, and correlated gives you context-aware filtering. Used wisely, they make your SQL cleaner, more maintainable, and responsive to changing data.

They might seem tricky at first, but once you get how each type behaves—and when to use them—you’ll wonder how you ever wrote SQL without them.

The above is the detailed content of How do subqueries (scalar, multi-row, correlated) enhance Oracle SQL capabilities?. For more information, please follow other related articles on the PHP Chinese website!