MySQL Subquery

MySQL Subquery

A subquery in MySQL is a query nested inside another query. Subqueries allow you to perform operations where the result of one query is used as input for another query.

Key Features

1. Nesting: Subqueries can be nested within SELECT, FROM, or WHERE clauses.
2. Multiple Levels: Subqueries can be nested up to 64 levels.
3. Return Types:
   • A single value (scalar).
   • A list of values.
   • A table (used in the FROM clause).

Types of Subqueries

1. Single-Row Subquery: Returns one row with one or more columns.
2. Multi-Row Subquery: Returns multiple rows.
3. Multi-Column Subquery: Returns multiple columns.
4. Correlated Subquery: Refers to columns in the outer query.

Syntax

Subquery in SELECT

SELECT column1, (SELECT aggregate_function(column2) FROM table2) AS alias
FROM table1;

Subquery in WHERE

SELECT column1
FROM table1
WHERE column1 = (SELECT column2 FROM table2 WHERE condition);

Subquery in FROM

SELECT column1
FROM (SELECT column2 FROM table2 WHERE condition) AS alias;

Example Table

Table: employees

+----+----------+------------+-------+
| id | name     | department | salary|
+----+----------+------------+-------+
| 1  | Alice    | IT         | 6000  |
| 2  | Bob      | HR         | 4000  |
| 3  | Charlie  | IT         | 5500  |
| 4  | Diana    | HR         | 4500  |
| 5  | Edward   | Sales      | 5000  |
+----+----------+------------+-------+

Table: departments

+----+------------+----------+
| id | name       | budget   |
+----+------------+----------+
| 1  | IT         | 15000    |
| 2  | HR         | 10000    |
| 3  | Sales      | 12000    |
+----+------------+----------+

1. Subquery in the SELECT Clause

Find employees and the budget of their department:

SELECT 
    name, 
    department, 
    (SELECT budget FROM departments WHERE name = employees.department) AS department_budget
FROM employees;

Result

+----------+------------+------------------+
| name     | department | department_budget|
+----------+------------+------------------+
| Alice    | IT         | 15000           |
| Bob      | HR         | 10000           |
| Charlie  | IT         | 15000           |
| Diana    | HR         | 10000           |
| Edward   | Sales      | 12000           |
+----------+------------+------------------+

2. Subquery in the WHERE Clause

Find employees with a salary greater than the average salary:

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

Result

+----------+--------+
| name     | salary |
+----------+--------+
| Alice    | 6000   |
| Charlie  | 5500   |
+----------+--------+

3. Subquery in the FROM Clause

List departments and their average employee salary:

SELECT name, avg_salary
FROM (
    SELECT department AS name, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS department_avg;

Result

+------------+------------+
| name       | avg_salary |
+------------+------------+
| IT         | 5750       |
| HR         | 4250       |
| Sales      | 5000       |
+------------+------------+

4. Correlated Subquery

Find employees who earn more than the average salary of their department:

SELECT name, salary, department
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

Result

plainte
+----------+--------+------------+
| name     | salary | department |
+----------+--------+------------+
| Alice    | 6000   | IT         |
| Diana    | 4500   | HR         |
+----------+--------+------------+

5. Multi-Row Subquery

Find departments where employees earn more than $5,000:

SELECT DISTINCT department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    WHERE salary > 5000
);

Result

+------------+
| department |
+------------+
| IT         |
| HR         |
+------------+

6. Subquery with EXISTS

Check if the IT department has employees:

SELECT 'IT department has employees' AS result
WHERE EXISTS (
    SELECT 1
    FROM employees
    WHERE department = 'IT'
);

Result

+---------------------------+
| result                    |
+---------------------------+
| IT department has employees |
+---------------------------+

Performance Tips

1. Indexing:

   • Use indexes on columns involved in subqueries to improve performance.

2. Avoid Correlated Subqueries:

   • Correlated subqueries are executed for each row in the outer query, which can be slow.

3. Replace with Joins:

   • If possible, replace subqueries with JOINs for better performance.

4. Use EXPLAIN:

   • Analyze the query execution plan to identify inefficiencies.

Use Cases

• Filtering with Aggregates:
  • E.g., employees earning above the average salary.
• Dynamic Conditions:
  • E.g., matching values from other tables or calculations.
• Complex Reports:
  • E.g., combining summarized and detailed data.

Let me know if you'd like to explore more examples or have questions!