Multi-Row Subqueries

A multi-row subquery is a subquery that returns multiple rows and is typically used to compare one value against multiple values from the outer query. This differs from a single-row subquery, which returns only one row.

Types of Multi-Row Subqueries

1.     IN Subquery

2.     ANY/ALL Subquery

3.     EXISTS Subquery (although this can return a boolean, it's typically considered in the broader context of multi-row operations)

4.     NOT IN Subquery

Let's go into each one in more detail.

 

1. IN Subquery

A multi-row subquery with the IN operator is used when you want to match a value against a list of values returned by the subquery.

Syntax:

SELECT column_name

FROM table_name

WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

Example:

SELECT employee_id, name

FROM employees

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1400);

·        In this example, the outer query retrieves employees who work in departments that are located in location 1400. The subquery returns the department_ids for departments located in 1400, and the outer query compares department_id with those returned by the subquery.

Characteristics:

·        Return Type: The subquery returns a list (or set) of values.

·        Usage: The IN operator checks if a value in the outer query matches any of the values returned by the subquery.

·        Efficiency: The IN operator is typically used when you're checking for values that belong to a specific set.

 

2. ANY/ALL Subqueries

The ANY and ALL operators are used to compare a value against a set of values returned by the subquery. They are used with comparison operators like =, >, <, >=, <=, and <>.

ANY Subquery

·        The ANY operator compares the outer query value with at least one value returned by the subquery.

Syntax:

SELECT column_name

FROM table_name

WHERE column_name operator ANY (SELECT column_name FROM another_table);

Example:

SELECT product_name, price

FROM products

WHERE price > ANY (SELECT price FROM products WHERE category = 'Electronics');

·        Here, the outer query returns products where the price is greater than at least one price in the 'Electronics' category.

ALL Subquery

·        The ALL operator compares the outer query value with all values returned by the subquery.

Syntax:

SELECT column_name

FROM table_name

WHERE column_name operator ALL (SELECT column_name FROM another_table);

Example:

SELECT product_name, price

FROM products

WHERE price > ALL (SELECT price FROM products WHERE category = 'Electronics');

·        In this case, the outer query returns products where the price is greater than all prices in the 'Electronics' category.

Characteristics:

·        ANY: The condition is true if at least one value from the subquery meets the comparison criteria.

·        ALL: The condition is true if all values from the subquery meet the comparison criteria.

 

3. EXISTS Subquery

The EXISTS operator is used to test whether the subquery returns any rows. While not specifically a "multi-row" subquery in the same way IN or ANY/ALL is, it's often used in scenarios where we want to check for the existence of rows.

Syntax:

SELECT column_name

FROM table_name

WHERE EXISTS (SELECT 1 FROM another_table WHERE condition);

Example:

SELECT employee_id, name

FROM employees

WHERE EXISTS (SELECT 1 FROM departments WHERE employees.department_id = departments.department_id);

·        This query returns all employees who belong to at least one department. The subquery checks for the existence of a department record that matches the employee's department.

Characteristics:

·        Return Type: EXISTS returns TRUE if the subquery returns any rows and FALSE otherwise.

·        Usage: Ideal for checking the existence of related records.

·        Efficiency: EXISTS can often be more efficient than IN when dealing with large datasets because it stops once it finds the first matching record.

 

4. NOT IN Subquery

The NOT IN operator is used when you want to retrieve rows where the value does not match any value returned by the subquery.

Syntax:

SELECT column_name

FROM table_name

WHERE column_name NOT IN (SELECT column_name FROM another_table WHERE condition);

Example:

SELECT employee_id, name

FROM employees

WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location_id = 1400);

·        This query returns employees who do not work in departments located in 1400.

Characteristics:

·        Return Type: Similar to IN, but the condition is negated.

·        Usage: Useful for filtering out records that match a set of values returned by the subquery.

 

Multi-Row Subquery Use Cases

1.     Finding Records in One Table That Match a Set of Criteria in Another Table:

o   Example: Finding employees working in departments with specific characteristics (e.g., location, budget).

2.     Comparing One Column to Multiple Values:

o   Example: Identifying products priced above the highest price in a certain category.

3.     Filtering Records Based on Relationships:

o   Example: Retrieving students who have taken at least one course in a certain department.

 

Key Points to Remember

·        Performance: Multi-row subqueries can sometimes be inefficient. It’s often recommended to check if there’s a way to convert them into a JOIN or EXISTS condition, which can improve performance in some cases.

·        NULL Values: Subqueries can return NULL values, which can lead to unexpected results, especially when using IN or NOT IN. Always be careful when working with nullable fields.

·        Correlated vs Non-Correlated: A correlated subquery references columns from the outer query, whereas a non-correlated subquery is independent of the outer query and can be executed on its own.

 

Example of a Correlated Multi-Row Subquery:

A correlated subquery means the subquery refers to the outer query in its WHERE clause.

SELECT employee_id, name

FROM employees e

WHERE e.salary > ALL (SELECT salary FROM employees WHERE department_id = e.department_id);

Here, the subquery references e.department_id from the outer query. This means the subquery is executed for each row of the outer query.

 

Conclusion

Multi-row subqueries in Oracle allow you to perform powerful comparisons between values from one table and a set of values derived from another table. They are versatile and can be used with operators like IN, ANY, ALL, and EXISTS. However, it’s essential to consider performance when using them, especially with large datasets, and to understand the context in which to use each subquery type to get the best results.