SQL Multiple-Row Subquery

Multiple-Row Subquery is a type of subquery that returns multiple rows of data to the outer query.

---

1. Overview

A Multiple-Row Subquery is a subquery that returns more than one row of data to the outer query. It is commonly used in conditions where multiple values are expected or allowed, such as the IN or NOT IN operators, or when performing set-based operations.

Example:

// Example of a multiple-row subquery used in a WHERE clause with the IN operator
SELECT product_name
FROM products
WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

In this example, the multiple-row subquery retrieves all category IDs for the 'Electronics' category, which are then used as conditions to select the corresponding product names.

---

2. Syntax

The syntax for a Multiple-Row Subquery is similar to that of a standard subquery, but it can return multiple rows:

SELECT column_name
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name);

Where the subquery returns multiple rows of data.

---

3. Usage

Multiple-Row Subqueries are commonly used in scenarios where multiple values need to be compared or selected based on specific conditions. They are often used in conjunction with the IN or NOT IN operators, or when performing set-based operations such as UNION, INTERSECT, or MINUS.

Example:

// Example of using a multiple-row subquery to retrieve product details based on multiple categories
SELECT product_name, category_id
FROM products
WHERE category_id IN (SELECT category_id FROM categories WHERE category_name IN ('Electronics', 'Clothing'));

This example retrieves product names and their corresponding category IDs for products belonging to either the 'Electronics' or 'Clothing' categories.

---

4. Conclusion

Multiple-Row Subqueries are a powerful feature in SQL for retrieving or filtering data based on conditions that yield multiple results. By returning multiple rows of data, they enable efficient selection and comparison of data within SQL queries.