The Easiest Guide to Master MySQL Subqueries (2025)

When querying with MySQL, there are scenarios where the simple queries just won’t cut it. Using complex operations or multi-step processes can make queries lengthy and harder to read.

✨ Subqueries — A powerful way to streamline your queries, improve readability, and perform operations in alternative and efficient ways.

In this article, we’ll dive into the world of MySQL subqueries.

🗂️What’s Inside this Article?

1️⃣Introduction on Subquery📘

2️⃣Where Subqueries can occur?📍

3️⃣Key features of Subqueries🧩

4️⃣Advantages of using Subqueries✅

5️⃣Different types of Subqueries🔄

6️⃣Conclusion🎯

🚀🚀Let’s get started!

1️⃣Introduction on Subquery📘

First, we’ll see what is a Subquery. So,

A subquery is a query inside another query that acts as input or output for that query. Such a Subquery is called an Inner Query or a Nested Query, while the query that contains a subquery is called an Outer Query.

2️⃣Where Subqueries can occur?📍

• It can be used with SELECT, FROM, and WHERE clauses.
• It can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET or DO statement or inside another subquery.
• It can be used with GROUP BY command but it cannot be used with ORDER BY command.

3️⃣Key Features of Subqueries🧩

• Subqueries must always enclosed in parentheses.
• Subqueries may be Correlated or Uncorrelated.
  – Correlated subqueries are dependent on the outer query for their execution.
  – Uncorrelated subqueries are independent; they do not depend upon an outer query for their execution.
• Subqueries can return various types of results:
  – Scalar ( A single value)
  – Row (A single row of data)
  – Column (A single column of data)
  – Table (One or more rows and columns)
• Based on the subquery result type, they are called Scalar, Row, Column, and Table subqueries.
• The inner query executed first, gives the result to the outer query; then the outer query will be executed.
• The subqueries are used to perform operations that are more complex and require multiple steps.
• If the main query does not have multiple columns for the subquery, then a subquery can have only one column in the SELECT command.
• If we use a subquery in a SET function, it cannot be immediately enclosed in a set function.

4️⃣Advantages of using Subqueries✅

• Subqueries provide alternative ways to perform operations where that require complex joins and unions.
• Subqueries make the queries in a structured form, so that is possible to isolate each part of a statement.
• Subqueries are more readable than complex joins or unions.
• Subqueries are used to enforce referential integrity when foreign keys are not implemented.

5️⃣Different types of Subqueries🔄

Before discussing different types of subqueries, Let’s create a simple table to perform upcoming operations. So here, we’ll create the table Employee.

CREATE TABLE Employee (
  Id int PRIMARY KEY,
  Name varchar(20),
  Salary int,
  DoB date,
  Department varchar(30)
);

INSERT INTO Employee (Id, Name, Salary, DoB, Department)
VALUES (101, 'Jack', 2000, '1997-05-19', 'HR'),
(102, 'Jack', 6000, '1997-03-10', 'HR'),
(103, 'Mack', 4000, '1998-07-16', 'Developer'),
(104, 'Peter', 3000, '1998-11-03', 'Tester'),
(105, 'Tom', 2500, '1997-10-10', 'HR'),
(106, 'Leo', 5300, '1999-01-05', 'Developer'),
(107, 'Roger', 2000, '1996-08-14', 'Accounts'),
(108, 'Mike', 4800, '1998-12-05', 'Tester'),
(109, 'Paul', 2000, '1994-02-15', 'Developer'),
(110, 'Hannah', 1500, '1998-01-20', 'HR');

Now we’ll discuss the different types of Subqueries.

1. Scalar subquery ( Single row)

Scalar Subquery is the type of subquery that returns a single value or single row with one column in the output.

Mostly the Aggregate functions ( SUM, MIN, MAX, AVG ) are used with Scalar subquery.

It also can be a query with an expression that returns a single value as output.

As a Scalar subquery is expecting a single value, It’s typically used with a SELECT statement or WHERE / HAVING clause.

When the subquery is used with a WHERE / HAVING clause, a comparison operator is always used.

Example Question –

❓Retrieve the Names of employees whose Salary is Less Than the Average Salary of all employees in the Employee table.

We can break down this question into two sub-questions. As,

➡️Retrieve the Average Salary of all Employees

➡️Retrieve the Names of Employees whose Salary is Less Than the Average Salary of all employees

First, we can write the query to retrieve the Average Salary of all Employees.

SELECT AVG(Salary) FROM Employee;

Then we can display the Names of Employees whose Salary is Less Than the Average Salary of all Employees. So here, we are using the above query as the Subquery by placing it inside parenthesis.

SELECT Name FROM Employee 
WHERE Salary < (
  SELECT AVG(Salary) FROM Employee
);

So here, It will first execute the Scalar Subquery and as a result, it will return the Average Salary of all Employees. Then that Average Salary value will be assigned to the WHERE clause of the Main query and the Main query will display only the Names of Employees who will only satisfy this WHERE condition; the Salary less than the Average salary.

2. Multiple row subquery (Multi-row)

A multi-row subquery is a subquery that returns multiple rows of data in the output with one column.

The output of a multi-row subquery can be used as a set of values for comparison, filtering, or other operations in an outer query.

The operators in the following table are used with multi-row subquery.

Example Question –

❓Retrieve the Names of Employees who work in Departments with more than 2 Employees are working.

We can break down this question into two sub-questions. As,

➡️Find all the Names of Departments where more than 2 Employees work

➡️Find the Names of Employees who work in those Departments, where more than 2 Employees are working.

First, we can write the query to retrieve the Names of Departments where more than 2 Employees work.

SELECT Department FROM Employee
GROUP BY Department 
HAVING COUNT(*)>2;

Then we can display the Names of Employees who work in those Departments. So here, we are using the above query as the Subquery by placing it inside parenthesis.

SELECT Name, Department FROM Employee 
WHERE Department IN (
  SELECT Department FROM Employee
  GROUP BY Department 
  HAVING COUNT(*)>2 
);

So here, It will First execute the Multi-row Subquery and as a result, it will return the list of Departments having more than 2 Employees working. Then that list will be assigned to the WHERE clause of the Main query using the IN Operator because it can pass a list of values for this operator for the comparison. Now the Main query will display only the Names and the Departments of Employees who will only satisfy this WHERE condition; the Departments where more than 2 Employees work.

3. Correlated subquery

This is a subquery that which the main query and subquery depend on each other.

This refers to one or more columns from the outer query to filter within its own query block.

It’s evaluated for each row of the outer query and can be used to filter or retrieve data based on the value of the outer query.

Example Question –

❓Retrieve the details of those Employees who have a Salary Higher Than the Average Salary of Employees in their respective Departments.

Now What the question is for………..?

If we take the First row in the Employee table; The Employee is named ‘Jack’ with a Salary of ‘2000’ and in the ‘HR’ Department. We need to check,

Now when we break this,

➡️Retrieve the Average Salary of the Employees from the HR Department

SELECT AVG(Salary) FROM Employee 
WHERE Department = 'HR';

➡️Compare & Display If the Salary of Employee ‘Jack’ is Higher Than the Average Salary of Employees in the ‘HR’ Department.

SELECT * FROM Employee
WHERE Salary > (
  SELECT AVG(Salary) FROM Employee 
  WHERE Department = 'HR';
);

Now will see; How we can write the query to solve this question.

SELECT * FROM Employee e1
WHERE Salary > (
  SELECT AVG(Salary) FROM Employee e2
  WHERE e1.Department = e2.Department;
);

So here, When the above query runs, it will check each and every row in the table one by one. First, it will execute the First row with the Id = ‘101’ and it will display all the columns from that row. Then with the WHERE clause, it will check the Subquery. The Subquery will calculate the Average Salary of Employees from the Employee table with the condition that says, ‘e1.Department = e2.Department’.

As currently it’s processing with the First row of the Employee table; the e1.Department = ‘HR’. So to calculate the Average salary; the e2.The department must be equal to the ‘HR’ Department to get this WHERE condition true. Finally, it will check whether the Salary of Employee ‘101’ is Higher Than the Average Salary or Not.

6️⃣Conclusion🎯

• MySQL subqueries are a versatile tool that significantly enhances the flexibility and power of your database queries, by enabling the nesting of queries.
• They allow to simplify complex operations, improve query readability, and break down intricate problems into manageable steps.
• Whether it’s Scalar, Multi-row, or Correlated Subqueries; understanding their key features and applications empowers to handle various data retrieval scenarios efficiently.
• Mastering Subqueries not only streamlines the database interactions but also equips advanced techniques to solve real-world database challenges effectively.

Happy Querying!

I hope you found this article helpful and insightful. If you did, feel free to share your thoughts and support:

🌟 Like this post to show your appreciation
📲 Share it with others who might find it useful
🔗 Follow me for more content like this
🖋️ Check out my other articles on https://medium.com/@cnirmani1997

Your support means a lot — thank you for reading! 😊