How to execute subqueries in MySQL 8: A Practical Guide

Introduction

Subqueries are a powerful feature in MySQL that allows you to perform complex data retrieval operations. This practical guide will walk you through the process of writing effective subqueries in MySQL 8. From basic concepts to advanced techniques, you will learn how to leverage subqueries to simplify your queries and improve your database interactions.

Understanding Subqueries

A subquery, also known as a nested query or inner query, is a query that is contained within another SQL query. The result of a subquery is used by the outer query to complete its execution. Subqueries can be used in various clauses such as SELECT, FROM, WHERE, and HAVING, and can return a single value, a single row, multiple rows, or multiple columns.

Let’s start with basic examples and gradually move to more advanced scenarios.

Basic Subquery

SELECT employee_id, name, department_id
FROM employees
WHERE department_id = (SELECT department_id
                      FROM departments
                      WHERE name = 'Marketing')

In the above example, the subquery retrieves the department ID for the ‘Marketing’ department, and the outer query uses this ID to retrieve all employees belonging to that department.

Subquery in the FROM Clause

SELECT dept.name, employee_total
FROM (
    SELECT department_id, COUNT(*) as employee_total
    FROM employees
    GROUP BY department_id
) AS dept_summary
JOIN departments AS dept
ON dept_summary.department_id = dept.department_id

This query creates an inline view (temporary table) named ‘dept_summary’ that contains the number of employees in each department, and then joins this inline view with the ‘departments’ table to display the department names alongside their total number of employees.

Scalar Subquery

SELECT name, (
    SELECT MAX(salary)
    FROM employees
    WHERE employees.department_id = d.department_id
) AS max_salary
FROM departments AS d

A scalar subquery returns a single value. In this example, for each department listed in the outer query, the subquery finds the maximum salary of employees in that department.

Correlated Subqueries

A correlated subquery is a subquery that references columns from the outer query. Unlike a simple subquery, a correlated subquery cannot be executed independently of the outer query.

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary)
                  FROM employees e2
                  WHERE e2.department_id = e.department_id)

This query retrieves the names and salaries of employees who earn more than the average salary in their respective departments.

Subqueries in the SELECT Clause

Subqueries can also be used in the SELECT clause to calculate additional columns based on other values.

SELECT name, (SELECT COUNT(*)
              FROM employees e
              WHERE e.department_id = d.department_id) AS employee_count
FROM departments d

This query lists all departments along with the count of employees in each department fetched by the subquery.

Subqueries with EXISTS

The EXISTS operator is used to test for the existence of any records in a subquery. This is particularly useful for checking relationships between tables.

SELECT name
FROM departments d
WHERE EXISTS (SELECT 1
              FROM employees e
              WHERE e.department_id = d.department_id
              AND e.salary > 100000)

The above query lists the names of departments that have at least one employee earning more than $100,000.

Advanced Subquery Techniques

Subqueries can be as complex as needed and may include multiple levels of nesting, utilize operations such as JOINs, or even modify data using INSERT, UPDATE, and DELETE commands.

Example with multi-level subquery:

SELECT name, (SELECT AVG(salary)
              FROM (SELECT salary
                    FROM employees e
                    WHERE e.department_id = d.department_id
                   ) AS dept_salaries)
FROM departments d

In the above multi-level subquery, the innermost subquery selects the salaries of all employees in each department, the middle subquery calculates the average of these salaries, and the outer query lists departments and their average salaries.

Subquery with JOIN:

SELECT e1.name, e1.salary
FROM employees e1
JOIN (SELECT department_id, MAX(salary) as max_salary
      FROM employees
      GROUP BY department_id) AS dept_max
ON e1.department_id = dept_max.department_id
AND e1.salary = dept_max.max_salary

This query finds the highest-paid employee in each department by joining a subquery that calculates the maximum salary per department.

Conclusion

Subqueries in MySQL 8 are a robust tool that can greatly enhance the capabilities of your SQL queries. Whether you’re performing a basic operation or crafting a complex multi-level query, understanding subqueries will help you access your data in powerful new ways. Remember to optimize your subqueries for the best performance and readability.