EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Subquery
 

MySQL Subquery

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 11, 2023

MySQL Subquery

 

 

Introduction to MySQL Subquery

In MySQL, a Subquery has defined as a SELECT SQL Statement used inside another SQL statement to calculate the results of outer queries. A Subquery, in SQL, is an inner query that is placed within an outer SQL query using different SQL clauses like WHERE, FROM, and HAVING, along with statement keywords such as SELECT, INSERT, FROM, UPDATE, DELETE, SET, or DO, and accompanied with expressional operators or logical operators.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Following are the types of subquery with respective result details:

  1. Single Row Subquery: It either returns zero or a single row
  2. Multiple Row Subquery: It returns one or multiple rows of a table
  3. Multiple Column Subquery: It returns one or multiple columns
  4. Correlated Subqueries refer to one or more columns in the outer SQL query.

Syntax of MySQL Subquery

We can say that a Subquery or Inner Query is a SQL query that can be used as a condition for the main SQL query outside that helps to restrict the data rows to be fetched from the tables embedded within the WHERE clause. This query could not be bounded in a set function.

For example:

SELECT * FROM tablename1 WHERE column1 = ANY (SELECT column1 FROM tablename2 WHERE tablename2……)

So, MySQL looks outside the subquery and finds tablename1 in the outer query.

Thus, like JOINS, a Subquery combines table data from different tables into a single result set. But JOINS returns rows, and the subquery returns either a single value as a result of a row set. Hence, the primary use of subquery can be to compute an instant value for another query to be executed.

Working on MySQL Subquery

Let us explain how to use the MySQL Subquery to write any compound queries and learn the correlated Subquery concept.

In MySQL, we can use subqueries in any SQL query expression, but we need to enclose them within parentheses.

Suppose we have the following SQL query, which provides us with the persons who work in the IT sector located in Delhi.

SELECT PersonName FROM PersonDb WHERE PersonID IN (SELECT PersonID FROM PersonIT WHERE City='Delhi');

In the above SQL Statement, the subquery first returns all the PersonIDs of PersonIT located in the city of Delhi. Then the outer query selects all the names from the PersonDb table that work in PersonIT whose PersonID is matched from the result set of the inner query. When we run this query, we can say that the query inside the outer executes first, and the result it returns is used as input for the outer query.

Types of MySQL Subquery

Following are the types of subquery with syntaxes and examples:

1. MySQL subquery on WHERE Clause

Let us prepare a demo data database table named Customer, having information about customers and payment amounts.

The table has CustomerID, Name, Payment_purpose, CustomerNum, and Amount fields.

CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, Name varchar(255), CustomerNum int, Payment_purpose varchar(255), Amount int );

And also inserted some data samples as follows:

INSERT INTO Customers (CustomerID, Name, CustomerNum, Payment_purpose , Amount) VALUES ('1', 'Nikhil', '21', 'Rent', '4000');

MySQL Subquery sample

We have used the following query to fetch the details required using a Subquery with comparison operators to return a value and with the expression in the WHERE clause:

SELECT Name, CustomerNum, Payment_purpose FROM Customers WHERE amount = (SELECT MAX(amount) FROM Customers);

Output:

MySQL Subquery output 1

This subquery with the WHERE clause returns the customer details whose payment amount is maximum in the table Customer.

In addition to this, you can also execute a subquery using the equality operator and another comparison like greater than (>) or smaller than (<).

For example, using a subquery, let’s use a subquery to find the customer details from the table where the payment amount is greater than average.

SELECT Name, CustomerNum, Payment_purpose FROM Customers WHERE amount > (SELECT AVG(amount) FROM Customers);

Here, we have used the AVG aggregate function in SQL inner query statement, which produces the following results through an outer query.

Output:

MySQL Subquery output 1.2

2. MySQL Subquery with operators IN and NOT IN

When a subquery provides more than a single value, you can add other operators with WHERE clauses, such as IN and NOT IN.

We have two tables suppose ‘Customers’ (CustomerID, Name, Payment_purpose, CustomerNum, Amount) and ‘Orders’ (OrderID, OrderNum, Status, CustomerNum).

CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY, CustomerNum int, status varchar (255), OrderNum int, Budget int );

MySQL Subquery output 2

We will first create the table, then insert some demo data.

Now let us find the customers who have not placed any orders using the subquery with the NOT IN operator.

SELECT Name, CustomerNum FROM Customers WHERE CustomerNum NOT IN (SELECT DISTINCT CustomerNum FROM Orders);

Output:

MySQL Subquery output 2.2

3. MySQL subquery with FROM clause

When using a subquery with the FROM clause, the database engine treats the resulting set as a temporary table where each row represents a record. We also refer to this temporary table as a derived or materialized subquery.

Using the subquery, we can also find the Minimum, Maximum, and Average number of items from the Budget table.

SELECT I.CustomerID, I.NAME, I.Payment_purpose, I.Amount FROM (select AVG(Budget) as averageBudget from Orders) as Budget, Customers as I WHERE I.Amount > Budget.averageBudget;

Output:

output 3

4. MySQL Correlated Subquery

As mentioned above, a correlated subquery is an independent query. It means that you can use it as a standalone query. The subquery evaluates the data from the outer query’s result set for each row.

For example,

SELECT OrderNum , COUNT(OrderNum) AS items FROM Orders GROUP BY OrderNum;

Output:

output 4

5. MySQL subquery with EXISTS & NOT EXISTS

The EXISTS and NOT EXISTS operators in SQL subquery return a Boolean value as a result, which can be either TRUE or FALSE. The following is the syntax to use EXISTS in a subquery:

SELECT * (column1, …) FROM Tablename WHERE EXISTS (subquery);

For example,

SELECT Name FROM Customer C WHERE EXISTS (SELECT * FROM Orders WHERE CustomerNum = C.CustomerNum);

Output:

output 5

EXISTS and NOT EXISTS check whether the rows exist or not in the result set and return TRUE or FALSE values, respectively.

Conclusion

In MySQL, you can nest a subquery inside the WHERE clause of an outer SQL SELECT statement.. The subquery can be added to further filter the result set using comparison operators like <,>, =, IN or BETWEEN, etc.

The database management system executes the Subquery first and then uses its result to generate the result set for the outer query. Thus, the statement is true. The SQL Subquery can return a scalar, column, row, and table subqueries in the result set rows, providing a condition for outer query and giving the final required result from the table in MySQL.

Recommended Articles

We hope that this EDUCBA information on “MySQL Subquery” benefited you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Aggregate Function
  2. Cursor in MySQL
  3. Guide to MySQL IN Operator
  4. MySQL Timestamp | How to Work?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW