How to Use a Subquery in MySQL

How to Use Subquery in MySQL 

In this tutorial, we will show you how to use the subquery in MySQL.

What is the Subquery? A MySQL subquery is a query nested inside the other query like Select, Delete,  Update and Insert.  Suppose we have the database table as something like below:

Let's start to understand how the subquery works in MySQL.

As per the above table structure, I just want to get the 2nd and the nth highest salary of the employee.

Step: 1

select max(salary) from Employee;

If you run the above query, you will get a result that looks something like 8,000. It's fine that we get the highest salary from the database, but what if you want to get the 2nd highest salary from the database? for the 2nd highest salary query, let's move on to the 2nd step. 

Step: 2

select max(salary) from Employee where salary < ( select max(salary) from Employee );

When you try to execute the above query, you will get the 2nd highest salary from the database i.e 7,000.  Now we are going to get the nth salary from the database. Nth means 3rd, 4th, 5th, etc.

Step: 3

select DISTINCT salary from Employee where salary < ( select max(salary) from Employee ) ORDER BY Salary DESC LIMIT 1 OFFSET 3;

The above query will return the 6000. It returns 6000 because we use the offset 3, which is the same as if you want to get the 4 and 5. Just replace the 3 with 4 and 5. We can also use the other easy way to get the highest salary from the database because as you know, in this article, we are explaining the subquery, so that’s why I designed the query. Let's try the other way to get the highest and the nth highest salary.

For more details, please go here.