Single-Row and Multiple-Row Subqueries in MySQL

Course: MySQL Training

Topic: Single-Row and Multiple-Row Subqueries

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Subqueries in MySQL

A subquery is a SQL query written inside another SQL statement. It is also called an inner query or nested query. The query containing the subquery is known as the outer query.

Subqueries allow you to retrieve a value or a set of values from one query and use those results in another query. They are useful for filtering records, comparing values with averages or maximums, finding related records, checking whether records exist, and solving complex data retrieval problems.

For example, suppose a company wants to find employees whose salaries are greater than the average salary of all employees. Instead of calculating the average manually, a subquery can calculate it and the outer query can compare every employee's salary with that result.

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

The inner query calculates the average salary. The outer query returns employees whose salaries exceed that average.

In MySQL, subqueries can return a single value, a single row, a single column containing multiple rows, or a table of multiple rows and columns. The type of result determines which comparison operators and query contexts are appropriate. Read the MySQL subquery documentation.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the meaning and structure of a subquery.
• Differentiate single-row and multiple-row subqueries.
• Write scalar subqueries that return a single value.
• Use comparison operators such as =, >, <, >=, and <= with single-row subqueries.
• Use IN, NOT IN, ANY, SOME, and ALL with multiple-row subqueries.
• Understand the role of aggregate functions inside subqueries.
• Write subqueries with WHERE, HAVING, and SELECT.
• Recognize common subquery errors and understand how to correct them.
• Handle NULL values and empty subquery results correctly.
• Compare subqueries with joins and apply performance best practices.

3. Understanding the Basic Structure of a Subquery

3.1 General Syntax

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

The main parts of a subquery are:

• Outer query: The main statement that returns the final result.
• Inner query: The query inside parentheses that supplies data to the outer query.
• Subquery result: The value, row, or set of rows produced by the inner query.
• Operator: The operator used to compare the outer query's values with the subquery result.
• Condition: The criteria used to retrieve the required data.

3.2 Example of a Simple Subquery

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

The subquery calculates the highest salary. The outer query returns employees whose salary equals that value. If multiple employees share the highest salary, all of them can be returned.

3.3 Why Use Subqueries?

• To compare records with aggregate values such as averages, maximums, and minimums.
• To retrieve records based on values from another table.
• To filter data using a list of values returned by another query.
• To find records that have or do not have related records.
• To separate a complex SQL problem into smaller logical steps.
• To calculate values that can be used by an outer query.

4. Types of Subqueries Based on Their Results

Subqueries can be classified by the number of rows and columns they return. Single-row and multiple-row subqueries are especially important when choosing comparison operators.

In everyday SQL lessons, a single-row subquery usually means a subquery expected to return one row or one scalar value. A multiple-row subquery returns a set of rows that must be handled with a suitable operator.

5. Creating Sample Tables for Practice

Use the following sample database to practice both single-row and multiple-row subqueries.

5.1 Create the Departments Table

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

5.2 Insert Department Records

INSERT INTO departments
(department_id, department_name, location)
VALUES
(1, 'IT', 'Mumbai'),
(2, 'HR', 'Pune'),
(3, 'Sales', 'Delhi'),
(4, 'Finance', 'Nashik');

5.3 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10,2),
    city VARCHAR(100),
    manager_id INT
);

5.4 Insert Employee Records

INSERT INTO employees
(employee_id, employee_name, department_id, salary, city, manager_id)
VALUES
(1, 'Priya', 1, 90000, 'Mumbai', NULL),
(2, 'Rahul', 1, 65000, 'Mumbai', 1),
(3, 'Amit', 1, 60000, 'Pune', 1),
(4, 'Neha', 2, 55000, 'Pune', 1),
(5, 'Karan', 3, 50000, 'Delhi', 2),
(6, 'Meera', 2, 58000, 'Mumbai', 1),
(7, 'Arjun', 3, 72000, 'Delhi', 2),
(8, 'Sara', 4, 68000, 'Nashik', 1);

5.5 View the Sample Data

SELECT * FROM departments;
SELECT * FROM employees;

These tables will be used throughout the notes to demonstrate how subqueries work with salary comparisons, department filtering, aggregate functions, and multiple-row results.

6. Single-Row Subqueries

A single-row subquery returns one row of data. When it returns exactly one column in one row, it provides a single value and is commonly called a scalar subquery.

Single-row subqueries are useful when a query needs to compare a column against one value, such as the highest salary, average salary, or the ID of a specific department.

6.1 Basic Syntax

SELECT column_name
FROM table_name
WHERE column_name comparison_operator (
    SELECT single_value
    FROM another_table
    WHERE condition
);

6.2 Common Operators for Single-Row Subqueries

7. Single-Row Subquery Using the Equality Operator

The equality operator = is used when the subquery returns a single value that can be compared with the outer query's column.

7.1 Find the Highest-Paid Employee

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

Expected output:

The subquery returns the maximum salary, which is 90000. The outer query finds the employee whose salary matches this value.

7.2 Find Employees in a Specific Department Using a Scalar Subquery

SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'HR'
);

This query works because the department name is unique in the sample data, so the subquery returns one department ID. If the subquery could return multiple department IDs, an operator such as IN would be more appropriate.

7.3 Important Rule

When using a scalar subquery with an ordinary comparison operator, make sure it returns at most one value. If it returns more than one row, MySQL can report an error such as Subquery returns more than 1 row.

8. Single-Row Subqueries With Aggregate Functions

Aggregate functions are often used in single-row subqueries because functions such as MAX(), MIN(), AVG(), and SUM() return one result value for the selected group.

8.1 Find Employees Earning More Than the Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary DESC;

The subquery calculates the average salary of all employees. The outer query returns employees whose salaries are greater than that average.

8.2 Find Employees Earning Less Than the Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary < (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary;

This query helps identify employees whose salaries are below the overall average. It does not automatically mean that their compensation is incorrect; the result is a comparison that may require further business context.

8.3 Find Employees With the Minimum Salary

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);

The query returns every employee whose salary equals the minimum salary. If multiple employees share that salary, all matching employees are returned.

8.4 Display the Average Salary Alongside Each Employee

SELECT
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS average_salary
FROM employees
ORDER BY employee_id;

In this example, the scalar subquery appears in the SELECT list. It supplies the average salary as an additional result column for each employee.

9. Single-Row Subqueries Using Different Comparison Operators

9.1 Greater Than (>)

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

Returns employees earning more than the average salary.

9.2 Less Than (<)

SELECT employee_name, salary
FROM employees
WHERE salary < (
    SELECT AVG(salary)
    FROM employees
);

Returns employees earning less than the average salary.

9.3 Greater Than or Equal To (>=)

SELECT employee_name, salary
FROM employees
WHERE salary >= (
    SELECT AVG(salary)
    FROM employees
);

Returns employees whose salaries are greater than or equal to the average.

9.4 Not Equal To (<>)

SELECT employee_name, salary
FROM employees
WHERE salary <> (
    SELECT MAX(salary)
    FROM employees
);

Returns employees whose salary differs from the maximum salary. If multiple employees earn the maximum salary, all of those employees are excluded.

10. Multiple-Row Subqueries

A multiple-row subquery returns more than one row. It is commonly used when the outer query needs to compare a value with a list of values, such as the department IDs of several departments or the IDs of customers who have placed orders.

Because a multiple-row subquery may return several values, a simple equality comparison such as department_id = (SELECT ...) is usually not appropriate unless the inner query is guaranteed to return at most one row.

10.1 General Syntax

SELECT column_name
FROM table_name
WHERE column_name IN (
    SELECT column_name
    FROM another_table
    WHERE condition
);

10.2 Common Operators for Multiple-Row Subqueries

11. Multiple-Row Subquery Using IN

The IN operator checks whether a value matches any value returned by a subquery. It is especially useful when the inner query returns a list of IDs.

11.1 Find Employees Working in Departments Located in Mumbai

SELECT employee_name, department_id, salary
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Mumbai'
);

In the sample data, the IT department is located in Mumbai, so employees whose department ID matches that department are returned.

11.2 Find Employees Working in Departments Located in Pune or Delhi

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location IN ('Pune', 'Delhi')
)
ORDER BY employee_name;

The inner query returns department IDs for departments located in Pune or Delhi. The outer query returns employees whose department IDs are in that set.

11.3 Find Employees in Departments With a Specific Name List

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('IT', 'Sales')
);

This query returns employees who belong to either IT or Sales. The inner query may return multiple department IDs, making IN suitable for the comparison.

12. Multiple-Row Subquery Using NOT IN

The NOT IN operator selects values that do not appear in the result set returned by a subquery. It is often used to find records that are not associated with a particular group.

12.1 Find Employees Who Are Not in IT or Sales

SELECT employee_name, department_id
FROM employees
WHERE department_id NOT IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('IT', 'Sales')
);

This query returns employees whose department IDs are not among the IDs of IT and Sales.

12.2 Important Warning About NULL

If the result of a NOT IN subquery contains NULL, comparisons may evaluate to UNKNOWN, causing expected rows to be excluded. This is an important source of confusion when working with nullable columns.

When the subquery column can contain NULL, filter NULL values if that matches the requirement, or consider using NOT EXISTS.

SELECT employee_name, department_id
FROM employees AS e
WHERE e.department_id NOT IN (
    SELECT d.department_id
    FROM departments AS d
    WHERE d.department_id IS NOT NULL
);

Because department_id is a primary key in the sample departments table, it cannot contain NULL. The explicit NULL filter is shown to demonstrate a defensive pattern when adapting this query to a different schema.

13. Multiple-Row Subqueries Using ANY and SOME

ANY and SOME compare a value against a set of values. The comparison succeeds when it is true for at least one value in the subquery result.

13.1 Find Employees Earning More Than Any Employee in the HR Department

SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = (
        SELECT department_id
        FROM departments
        WHERE department_name = 'HR'
    )
)
ORDER BY salary DESC;

The comparison salary > ANY (...) is true when an employee's salary is greater than at least one salary returned by the subquery. It does not mean the employee earns more than every HR employee.

13.2 Find Employees Earning Less Than Any Employee in IT

SELECT employee_name, salary
FROM employees
WHERE salary < ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 1
)
ORDER BY salary;

This query returns employees whose salaries are lower than at least one salary among employees in department 1.

13.3 Understanding SOME

SOME is a synonym for ANY in MySQL. The following two conditions have the same meaning:

salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 1
)

salary > SOME (
    SELECT salary
    FROM employees
    WHERE department_id = 1
)

14. Multiple-Row Subqueries Using ALL

The ALL operator compares an expression with every value returned by a subquery. The condition is true only when the comparison is true for all values in the result set.

14.1 Find Employees Earning More Than Every Employee in HR

SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 2
)
ORDER BY salary DESC;

This returns employees whose salaries are greater than every salary returned for HR. When the subquery contains non-NULL salary values, the condition is equivalent to being paid more than the highest salary in that set.

14.2 Find Employees Earning Less Than Every Employee in IT

SELECT employee_name, salary
FROM employees
WHERE salary < ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 1
)
ORDER BY salary;

This query returns employees whose salaries are lower than every salary returned for department 1.

14.3 ANY vs ALL

15. Multiple-Row Subqueries With EXISTS

EXISTS checks whether a subquery returns at least one row. It does not require the inner query to return a specific value because the existence of a row is what matters.

15.1 Find Employees Whose Department Exists in the Departments Table

SELECT
    e.employee_id,
    e.employee_name,
    e.department_id
FROM employees AS e
WHERE EXISTS (
    SELECT 1
    FROM departments AS d
    WHERE d.department_id = e.department_id
);

This query returns employees whose department ID matches a department record. It is particularly useful for checking whether a related record exists.

15.2 Find Employees Without a Matching Department

SELECT
    e.employee_id,
    e.employee_name,
    e.department_id
FROM employees AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM departments AS d
    WHERE d.department_id = e.department_id
);

This query identifies employees whose department ID does not match any department row. In the sample data, every non-NULL department ID has a matching department.

15.3 EXISTS vs IN

16. Subqueries in the SELECT Clause

A scalar subquery can be placed in the SELECT list to calculate an additional value for the output. It can be useful for displaying an overall statistic alongside individual records.

16.1 Display Each Employee With the Highest Salary

SELECT
    employee_name,
    salary,
    (SELECT MAX(salary) FROM employees) AS highest_salary
FROM employees
ORDER BY employee_id;

The subquery returns one value, which appears as the highest_salary column for each employee.

16.2 Compare Each Salary With the Overall Average

SELECT
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS average_salary,
    salary - (SELECT AVG(salary) FROM employees) AS difference_from_average
FROM employees
ORDER BY employee_id;

This query shows each employee's salary, the average salary, and the difference between the employee's salary and the average. For large or complex queries, consider whether calculating the same expression multiple times or using a derived table or common table expression would make the query easier to maintain.

17. Subqueries in the HAVING Clause

The HAVING clause filters groups created by GROUP BY. A subquery in HAVING can compare an aggregate value for each group with an overall or related aggregate value.

17.1 Find Departments With an Average Salary Above the Overall Average

SELECT
    department_id,
    AVG(salary) AS department_average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY department_id;

The inner query calculates the average salary for all employees. The outer query groups employees by department and retains only departments whose average salary is higher than the overall average.

17.2 Find Departments With More Than Two Employees

A grouped subquery can be used to find department IDs with a specified number of employees.

SELECT department_id, department_name
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 2
);

The inner query returns the IDs of departments with more than two employee records. The outer query displays the corresponding department names.

18. Subqueries in the FROM Clause

A subquery in the FROM clause creates a derived table. The outer query treats the subquery result like a table and can select, filter, or aggregate its columns.

18.1 Calculate Department Average Salaries

SELECT
    department_id,
    department_average_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS department_average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary
WHERE department_average_salary > 60000
ORDER BY department_id;

The inner query calculates the average salary for each department. The outer query filters the derived table to show only departments whose average salary exceeds 60000.

18.2 Why Is an Alias Required?

In MySQL, a derived table in the FROM clause must have a table alias. In the example, department_summary is the alias assigned to the result of the inner query.

19. Correlated and Non-Correlated Subqueries

Subqueries can also be classified according to whether they refer to the outer query.

19.1 Non-Correlated Subquery

A non-correlated subquery does not reference columns from the outer query. Its result is logically independent of the current outer row.

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

The average salary subquery does not depend on a particular employee row.

19.2 Correlated Subquery

A correlated subquery refers to one or more columns from the outer query. It evaluates its condition in relation to the current outer row, although MySQL may optimize the execution strategy.

19.3 Find Employees Earning More Than Their Department's Average

SELECT
    e.employee_name,
    e.department_id,
    e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id, e.salary DESC;

The inner query uses e.department_id from the outer query to calculate the average salary for that employee's department. Each employee is compared with the average for their own department rather than the average for the entire company.

19.4 Key Differences

20. Single-Row vs Multiple-Row Subqueries

21. Common Errors in Single-Row Subqueries

21.1 Error: Subquery Returns More Than One Row

A common error occurs when a scalar comparison expects one value but the inner query returns multiple rows.

Incorrect example:

SELECT employee_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE location IN ('Mumbai', 'Delhi')
);

The inner query may return more than one department ID. MySQL can raise an error because the equality comparison expects at most one scalar result.

Correct approach: Use IN when multiple department IDs are valid results.

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location IN ('Mumbai', 'Delhi')
);

21.2 Error: Assuming LIMIT Always Solves the Problem

Adding LIMIT 1 can force a query to return at most one row, but it is not automatically a correct solution. If multiple records are valid, choosing an arbitrary row can hide a logic problem.

SELECT department_id
FROM departments
WHERE location = 'Mumbai'
ORDER BY department_id
LIMIT 1;

This query selects the first matching department ID according to the specified ordering. Use this approach only when selecting one record is actually part of the requirement.

21.3 Error: Using an Aggregate Function Without Understanding NULL

Aggregate functions such as AVG, MIN, MAX, and SUM generally ignore NULL input values. If all values are NULL, or a query returns no matching rows, the aggregate result may be NULL. COUNT behaves differently and returns zero when no rows are counted.

SELECT AVG(salary)
FROM employees
WHERE department_id = 99;

If department 99 has no employees, the average salary is NULL. A comparison such as salary > (subquery) will not evaluate to TRUE for ordinary non-NULL salaries when the subquery result is NULL.

22. Common Errors in Multiple-Row Subqueries

22.1 Using Equality With a Multiple-Row Result

Using = with a subquery that returns several values may cause an error. Use IN, ANY, SOME, or ALL according to the intended comparison.

-- Suitable for multiple department IDs
SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location IN ('Mumbai', 'Delhi')
);

22.2 Misunderstanding ANY and ALL

> ANY means greater than at least one returned value. > ALL means greater than every returned value. Confusing these operators can produce results that are technically valid but do not meet the business requirement.

22.3 Ignoring NULL With NOT IN

If a NOT IN subquery returns NULL, the outer comparison may evaluate to UNKNOWN, causing rows to be excluded. Filter NULL values when appropriate or use NOT EXISTS for missing-relationship checks.

22.4 Returning Unnecessary Columns

When using IN, ANY, or ALL with a scalar comparison, the subquery generally needs to return one column. If you need to compare multiple columns together, MySQL supports row-constructor comparisons in appropriate contexts.

SELECT employee_id, employee_name
FROM employees
WHERE (department_id, city) IN (
    SELECT department_id, location
    FROM departments
);

This example illustrates the syntax for comparing pairs of values, but the columns must represent compatible data and the relationship must make business sense. In the sample schema, department location and employee city are not necessarily equivalent concepts, so this query should not be used as a meaningful business report without a suitable data model.

23. Subquery Performance and Optimization

Subqueries can make SQL easier to understand, but performance depends on the query structure, table size, indexes, data distribution, and MySQL's query optimizer. A subquery is not automatically slower than a join, and rewriting every subquery is not always beneficial.

23.1 Use EXPLAIN

EXPLAIN
SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

EXPLAIN shows how MySQL plans to execute the query. Review the access methods, possible indexes, selected indexes, and estimated rows to understand potential performance issues.

23.2 Index Columns Used in Filtering and Relationships

Indexes can improve queries that repeatedly filter or match values, but they should be chosen based on the actual workload. For example, an index on department_id may help queries that filter employees by department.

CREATE INDEX idx_employees_department_id
ON employees(department_id);

The index may benefit department-based filtering and correlated subqueries. The optimizer may choose a different strategy depending on the data and query.

23.3 Avoid Repeating Complex Expressions Unnecessarily

If a query uses the same complex aggregate or calculation several times, consider whether a derived table or common table expression would improve readability and maintainability. Check the execution plan rather than assuming that a rewrite is faster.

23.4 Understand MySQL's Optimization

MySQL may transform some subqueries into semijoins, antijoins, or other execution strategies. The actual execution method depends on the query and optimizer. Learn more in the MySQL subquery optimization documentation.

24. Practical Project: Employee Salary Analysis Using Subqueries

Project objective: Build a set of SQL reports that analyze salaries and departments using single-row and multiple-row subqueries.

24.1 Requirement 1: Find Employees Earning Above the Company Average

SELECT
    employee_id,
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary DESC;

24.2 Requirement 2: Find Employees Earning the Highest Salary

SELECT
    employee_id,
    employee_name,
    salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

24.3 Requirement 3: Find Employees in IT and Sales

SELECT
    e.employee_name,
    d.department_name,
    e.salary
FROM employees AS e
JOIN departments AS d
    ON e.department_id = d.department_id
WHERE e.department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('IT', 'Sales')
)
ORDER BY d.department_name, e.salary DESC;

24.4 Requirement 4: Find Employees Earning More Than Their Department Average

SELECT
    e.employee_name,
    e.department_id,
    e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id, e.salary DESC;

24.5 Requirement 5: Find Employees Earning More Than Every HR Employee

SELECT
    employee_name,
    salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = (
        SELECT department_id
        FROM departments
        WHERE department_name = 'HR'
    )
)
ORDER BY salary DESC;

24.6 Project Learning Outcomes

• Use scalar subqueries with aggregate functions.
• Filter records with IN and ALL.
• Compare individual values with group-level statistics.
• Understand correlated subquery behavior.
• Inspect results and validate the meaning of each condition.

25. Practice Exercises

Complete the following exercises using the sample employees and departments tables.

1. Find the employee or employees earning the highest salary using a single-row subquery.
2. Find employees whose salaries are below the overall average salary.
3. Find employees who work in departments located in Delhi.
4. Find employees who do not belong to IT or Sales using NOT IN, while considering NULL values.
5. Find employees whose salaries are higher than every employee in the HR department.
6. Find employees whose salaries are lower than at least one employee in the IT department.
7. Find departments that have more than two employees.
8. Display each employee's salary alongside the overall average salary.
9. Find employees who earn more than the average salary of their own department.
10. Use EXISTS to find employees with a valid matching department.
11. Use NOT EXISTS to find employees without a matching department.
12. Write a derived-table query that displays departments whose average salary is greater than 60000.

25.1 Challenge Exercise

Create a report that displays the employee name, department name, employee salary, department average salary, and difference between the employee's salary and their department's average. Use a correlated subquery for the average and sort the results by department and salary.

26. Interview Questions and Answers

Q1. What is a subquery in MySQL?

Answer: A subquery is a SQL statement nested inside another SQL statement. Its result is used by the outer query to filter, compare, calculate, or retrieve data.

Q2. What is a single-row subquery?

Answer: A single-row subquery returns at most one row. A scalar subquery returns one value and can be used with comparison operators such as =, >, <, >=, and <=.

Q3. What is a multiple-row subquery?

Answer: A multiple-row subquery returns a set of rows. Operators such as IN, NOT IN, ANY, SOME, and ALL are used to compare values with that set.

Q4. What is the difference between IN and =?

Answer: The equality operator compares a value with a single value in a scalar comparison. IN checks whether a value matches any value in a set of returned values.

Q5. What is the difference between ANY and ALL?

Answer: ANY succeeds when the comparison is true for at least one returned value. ALL succeeds only when the comparison is true for every returned value, subject to SQL's NULL logic.

Q6. What is a correlated subquery?

Answer: A correlated subquery references a column from the outer query. For example, it can calculate the average salary for the department of each employee being evaluated.

Q7. What happens when a scalar subquery returns multiple rows?

Answer: MySQL reports an error because a scalar comparison expects at most one value. If multiple values are intended, use an appropriate operator such as IN, ANY, or ALL.

Q8. Why can NOT IN return unexpected results?

Answer: If the subquery contains NULL, the NOT IN comparison can evaluate to UNKNOWN. Filter NULL values when appropriate or use NOT EXISTS.

Q9. Can a subquery use aggregate functions?

Answer: Yes. Functions such as AVG, MAX, MIN, SUM, and COUNT are frequently used to produce values for the outer query.

Q10. What is a derived table?

Answer: A derived table is a subquery placed in the FROM clause. It acts as a table expression and must have an alias in MySQL.

Q11. Are subqueries always slower than joins?

Answer: No. Performance depends on the query structure, indexes, data distribution, and optimizer decisions. Use EXPLAIN and realistic data to evaluate the query.

Q12. What is the difference between EXISTS and IN?

Answer: IN checks whether a value belongs to a set, while EXISTS checks whether the subquery returns a matching row. Both can be useful, and the best choice depends on the query's meaning and data.

27. Quick Revision Checklist

• A subquery is a query nested inside another SQL statement.
• A scalar subquery returns one value.
• A single-row subquery is commonly used with comparison operators.
• A multiple-row subquery can be used with IN, NOT IN, ANY, SOME, ALL, EXISTS, and NOT EXISTS.
• Use MAX(), MIN(), AVG(), SUM(), and COUNT() where appropriate.
• Use IN when the inner query can return several possible values.
• ANY means the comparison succeeds for at least one returned value.
• ALL means the comparison must succeed for every returned value.
• NOT IN requires careful handling of NULL values.
• A correlated subquery refers to the outer query.
• A derived table appears in the FROM clause and requires an alias.
• Use EXPLAIN and appropriate indexes to investigate performance.
• Validate that the result matches the intended business requirement.

28. Conclusion

Single-row and multiple-row subqueries are important MySQL techniques for retrieving and analyzing data. Single-row subqueries are useful for comparing values against one result, such as the highest salary or average salary. Multiple-row subqueries are useful for checking values against a set of results using IN, NOT IN, ANY, SOME, and ALL.

Subqueries can also be used with EXISTS, aggregate functions, SELECT, WHERE, HAVING, and FROM clauses. Understanding the expected number of rows, the meaning of each comparison operator, and the effects of NULL values helps prevent common errors.

With regular practice, you can use subqueries to write readable SQL queries, analyze employee and department data, compare values against group statistics, and solve more complex database problems.

Continue learning: Visit the MySQL Training Course or register for a course demo.