MySQL - SubQuery

MySQL: Mastering Subqueries and Their Alternatives

A subquery, also known as a nested or inner query, is a SELECT query placed inside another SQL query. They are a powerful tool for performing complex data retrieval by using the result of one query to filter or provide data for another. Subqueries can be used in SELECT, FROM, WHERE, INSERT, UPDATE, and DELETE statements.

Scenario Setup

For our examples, let’s assume we have two tables: employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');

INSERT INTO employees VALUES
(101, 'Alice', 90000, 1),
(102, 'Bob', 85000, 1),
(103, 'Charlie', 70000, 2),
(104, 'Diana', 72000, 2),
(105, 'Eve', 60000, 3);

Subquery in a WHERE Clause

The most common use of a subquery is to filter results in a WHERE clause. For example, let’s find all employees who work in the ‘Engineering’ department.

Using a Subquery with IN

The inner query finds the department_id for ‘Engineering’, and the outer query finds all employees matching that ID.

SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE department_name = 'Engineering'
);

Modern Alternative: Using a JOIN

For this type of problem, using a JOIN is often more readable and can be more performant, as the database optimizer is highly tuned for joins. It expresses the relationship between the tables more directly.

SELECT e.name, e.salary
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';

Both queries produce the same result, but the JOIN version is generally preferred.

name	salary
Alice	90000.00
Bob	85000.00

Subquery with Comparison Operators

You can also use subqueries that return a single value (a scalar subquery) with comparison operators like =, >, <, etc. Let’s find employees who earn more than the average salary of the entire company.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Modern Alternative: Common Table Expressions (CTE)

For more complex multi-step logic, nested subqueries can become difficult to read. MySQL 8.0 introduced Common Table Expressions (CTEs), which allow you to define named, temporary result sets. Let’s find employees in each department who earn more than their department’s average salary.

WITH DepartmentAvg AS (
    SELECT
        department_id,
        AVG(salary) as avg_dept_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.name,
    e.salary,
    d.department_name
FROM employees AS e
JOIN DepartmentAvg AS da ON e.department_id = da.department_id
JOIN departments AS d ON e.department_id = d.department_id
WHERE e.salary > da.avg_dept_salary;

CTEs make the logic much clearer by breaking it down into logical, reusable steps.

Subquery with an INSERT Statement

Subqueries can be used to populate a table with data from other tables. This is useful for creating backups or summary tables. Let’s create a backup of our engineering employees.

CREATE TABLE engineering_archive LIKE employees;

INSERT INTO engineering_archive (employee_id, name, salary, department_id)
SELECT employee_id, name, salary, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Engineering');

Using Subqueries via a Client Program

When executing subqueries from application code, the key is to use prepared statements to safely pass any variables into the query, preventing SQL injection.

Python: Finding Employees in a Specific Department

import mysql.connector

def get_employees_by_dept(department_name):
    results = []
    query = """
        SELECT name, salary
        FROM employees
        WHERE department_id IN (
            SELECT department_id FROM departments WHERE department_name = %s
        )
    """
    try:
        conn = mysql.connector.connect(user='root', password='my-secret-pw', database='your_db')
        cursor = conn.cursor(dictionary=True)

        cursor.execute(query, (department_name,))
        results = cursor.fetchall()

        print(f"Employees in {department_name}:")
        for row in results:
            print(f"- {row['name']}, Salary: {row['salary']}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
    return results

if __name__ == "__main__":
    get_employees_by_dept('Sales')

Node.js: Finding Employees Above a Certain Salary Threshold

const mysql = require('mysql2/promise');

async function findHighEarners(minSalary) {
  let connection;
  try {
    connection = await mysql.createConnection({ host: 'localhost', user: 'root', password: 'my-secret-pw', database: 'your_db' });

    // Using a JOIN is better here, but this shows a parameterized subquery.
    const query = 'SELECT name, salary FROM employees WHERE salary > ? ORDER BY salary DESC';
    const [rows] = await connection.execute(query, [minSalary]);

    console.log(`Employees earning more than ${minSalary}:`);
    rows.forEach(row => {
        console.log(`- ${row.name}, Salary: ${row.salary}`);
    });

  } catch (error) {
    console.error('Database query failed:', error);
  } finally {
    if (connection) await connection.end();
  }
}

findHighEarners(80000);