Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Use a Subquery in MySQL

DZone 's Guide to

How to Use a Subquery in MySQL

Let's take a look at a tutorial that explains how to use a subquery in MySQL and also view an example on how to find salaries.

· Database Zone ·
Free Resource

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:

ID Fname Lname Email Salary
1

Chandar

bhushan pbchandan3@gmail.com 8000
2 Pavan yadan pavan@gmail.com 7000
3 Gopal thakur goapla@gmail.com 6000
4 Sukhi saini sukhi@gmail.com 5000
5 test last tast@gmail.com 4000
6 new last new@gmail.com 3000

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

Topics:
mysql ,php ,database ,tutoiral ,subquery in mysql

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}