What is a subquery, and how do you use it in MySQL?

Subqueries, also known as nested queries, are queries embedded within another query in SQL statements. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements, often to provide data values for conditions. Subqueries are typically used in the WHERE or FROM clauses.

In MySQL, subqueries can be independent SELECT statements or partial queries, returning either a single value or a set of values depending on the usage context.

Types of Subqueries

1. Scalar Subquery: A subquery that returns a single value, typically used on the right side of comparison operators.
2. Row Subquery: A subquery that returns a single row with multiple columns.
3. Table Subquery: A subquery that returns a complete result set, often used with operators like IN, EXISTS, and NOT EXISTS.

Use Cases and Examples

1. As a Filtering Condition

You can use subqueries in the WHERE clause to filter the results of the main query. For example, suppose you want to query employee IDs where sales amounts exceed the average sales amount:

sql
SELECT employee_id, sales_amount
FROM sales
WHERE sales_amount > (
    SELECT AVG(sales_amount)
    FROM sales
);

Here, the subquery calculates the average sales amount for all sales records, and the main query filters employees by comparing each record's sales amount to this average.

2. As a Selection Column

Subqueries can also be used in the SELECT list to compute or extract additional information for each result row. For example, query each employee's sales amount and the average sales amount for their department:

sql
SELECT employee_id, sales_amount,
       (SELECT AVG(sales_amount) FROM sales WHERE department_id = s.department_id) AS department_avg
FROM sales s;

Here, for each record in the sales table, the subquery calculates the average sales amount for the same department.

3. As a Data Source

Subqueries can be used in the FROM clause, often referred to as derived tables or inline views. For example, query the IDs of the top three employees with the highest sales:

sql
SELECT employee_id
FROM (
    SELECT employee_id
    FROM sales
    ORDER BY sales_amount DESC
    LIMIT 3
) AS top_sales;

Here, the subquery first selects the top three employees with the highest sales from the sales table, and the main query returns these employee IDs.

Subqueries are a powerful tool in SQL, enabling more flexible and robust data queries. By nesting and combining multiple subqueries, complex query requirements can be solved.