MySql - What are subqueries

What Are Subqueries?

Introduction to Subqueries in MySQL

A subquery in MySQL is a SQL query nested inside another query. It is used to perform operations that require multiple steps, where the result of one query is used in another. Subqueries are also known as inner queries or nested queries, and the main query that contains the subquery is called the outer query.

Subqueries are incredibly powerful and flexible tools in MySQL that enable complex filtering, transformation, and analysis of data. They can be used in various SQL clauses, such as SELECT, FROM, WHERE, and HAVING.

Types of Subqueries

1. Single-Row Subquery

A single-row subquery returns only one row. It is used when you expect the subquery to return just one value. These are often used with comparison operators like =, !=, >, <, etc.

2. Multiple-Row Subquery

A multiple-row subquery returns more than one row. These subqueries are used with operators such as IN, ANY, or ALL.

3. Multiple-Column Subquery

This type of subquery returns more than one column. These are used when comparisons are required between multiple columns.

4. Correlated Subquery

A correlated subquery is a subquery that uses values from the outer query. It is executed once for every row selected by the outer query, which means it cannot be executed independently.

5. Nested Subquery

A nested subquery is when a subquery contains another subquery inside it. This allows for complex logical processing.

Basic Syntax of a Subquery

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

The subquery is always enclosed in parentheses. It can return either a scalar (single value), a list of values, or a result set depending on its usage.

Subqueries in SELECT Clause

A subquery can be used in the SELECT clause to return a computed column.

Example

SELECT 
    emp_name,
    (SELECT dept_name FROM departments WHERE departments.dept_id = employees.dept_id) AS department
FROM employees;

In this example, the subquery fetches the department name from the departments table for each employee.

Subqueries in WHERE Clause

This is the most common use of subqueries. It filters rows based on the results of the subquery.

Example

SELECT emp_name, salary
FROM employees
WHERE dept_id = (
    SELECT dept_id FROM departments WHERE dept_name = 'Finance'
);

Here, the subquery retrieves the dept_id for 'Finance', and the outer query fetches employees belonging to that department.

Subqueries with IN, ANY, ALL Operators

Using IN

SELECT emp_name
FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE location = 'New York'
);

Using ANY

SELECT emp_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE dept_id = 102
);

Using ALL

SELECT emp_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE dept_id = 103
);

ANY and ALL allow for flexible comparisons against a set of values returned by a subquery.

Correlated Subqueries

A correlated subquery is evaluated once for each row processed by the outer query. It references a column from the outer query.

Example

SELECT emp_name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE dept_id = e.dept_id
);

In this case, the subquery computes the average salary per department, and the outer query finds employees earning above that average in their respective departments.

Subqueries in the FROM Clause

Subqueries can be used in the FROM clause to create temporary tables or derived tables.

Example

SELECT dept_id, AVG(salary) AS avg_salary
FROM (
    SELECT dept_id, salary
    FROM employees
    WHERE salary > 3000
) AS high_earners
GROUP BY dept_id;

This query calculates the average salary of employees earning more than 3000 per department.

Subqueries in HAVING Clause

SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > (
    SELECT AVG(emp_count)
    FROM (
        SELECT dept_id, COUNT(*) AS emp_count
        FROM employees
        GROUP BY dept_id
    ) AS dept_counts
);

This example uses a subquery in the HAVING clause to compare group-wise counts against an average.

Real-World Use Cases

1. Finding Top Earners by Department

SELECT emp_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.dept_id = employees.dept_id
);

2. Identifying Departments Without Employees

SELECT dept_name
FROM departments
WHERE dept_id NOT IN (
    SELECT DISTINCT dept_id FROM employees
);

3. Listing Products That Never Sold

SELECT product_name
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id FROM sales
);

Subqueries vs. Common Table Expressions (CTEs)

Subqueries and CTEs both allow you to organize complex queries into manageable parts. However, CTEs offer advantages such as recursion and improved readability.

Example CTE

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
)
SELECT e.emp_name, e.salary
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

Subqueries are an essential part of the MySQL query language, providing the power to perform operations that require multiple steps, comparisons, and intermediate calculations. Whether you're filtering data, aggregating metrics, or combining tables, subqueries offer a flexible and often intuitive approach to expressing complex logic.

However, due to potential performance implications—especially with correlated subqueries—it's essential to use subqueries judiciously and consider alternatives like JOINs and CTEs when they offer better clarity or efficiency.