EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Subquery

MySQL Subquery

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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?
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests
 93+ Hour of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more