Logo
×

MySQL Subquery

The subquery is a MySQL query used to manage data operations. Mainly subquery helps to retrieve data with the necessary condition. It creates a nested query with two different queries. The primary query contains a secondary query using parenthesis. The primary query is called an outer query, whereas the second query is called the inner query. The subquery helps to search, filter, and sort the table data based on our needs.   

Rules of the MySQL Subquery

  • The inner query must place into the parenthesis of the outer query.
  • The inner query uses a single column when an outer query does not include them.
  • The subquery uses comparison operators like =, >, <, >=, <=.
  • It uses MySQL operators like IN, ANY, etc.
  • The outer query cannot use the "order by" operator. However, the inner query can use this operator.
  • Primarily, the inner query works with the "WHERE" clause and its condition.
  • The subquery operates a single table or multiple table information in a single query.

Syntax

The basic syntax of the subquery shows below.

MySQL outer query (MySQL inner query);

MySQL subquery syntax shows below.

SELECT column1, column2, column3 
FROM table-name 
WHERE column operator
  (SELECT column FROM table-name [WHERE condition]);

MySQL subquery with two tables syntax shows below.

SELECT column1, column2, column3 
FROM first table name 
WHERE column operator
  (SELECT column FROM second table name [WHERE condition]);

The outer query uses a primary table with the essential condition. The inner query uses either the primary table or the secondary table with the condition. The outer query clause uses conditions for the inner query.

Prerequisite

MySQL subquery needs to choose a database and table for operation on their data. The subquery requires a comparison operator, conditions, and clause. You can create a table and insert value in it. It is better to understand MySQL joins and MySQL key concepts.

Example of the MySQL Subquery

1) Example: The following example displays information using a subquery.

Execute the below query to display table information. The inner query uses the aggregate function "max" for the points column. The "points" column applies the "equal to" operator for the inner query result.

mysql> select topic, points
from student_data  
WHERE points = 
(select Max(points) from student_data );

OUTPUT

MySQL Subquery

The above output image shows the topic and the "points" field of the table. The maximum value of the "points" column is 44. Here, the "equal to" operator works with the inner query.

2) Example: The subquery with comparison operator example shows below.

Execute the below query to display table information where the inner query uses aggregate function max for the "points" column. Here we have the conditional operator "less than" with the points column.

mysql> select topic, points
from student_data  
WHERE points < 
(select Max(points) from student_data );

OUTPUT

MySQL Subquery

The above output image shows the topic and points field of the table. The "points" column is less than the maximum column, which is 44. Here, the "less than" operator works with the inner query. The maximum value does not display in the output.

3) Example: The subquery with the "MIN and MAX" aggregate function example shows below.

The below query uses the inner query with an aggregate function "MIN" for the grade column and “MAX” for the "points" column. The points column applies the "less than" operator.

mysql> select topic, points, MIN(grade) AS remark from student_data  WHERE points < (select Max(points) from student_data  order by points asc);

OUTPUT

MySQL Subquery

Executing the query will display the value of the points 40 and grade value A. Here, MIN and MAX aggregation uses simultaneously. 

4) Example: The subquery with the "IN" operator example shows below.

The below subquery uses the "IN" operator, and the condition applies to the "grade" column.

mysql> select topic, points, grade AS remark from student_data  where grade IN (select grade from student_data  where grade = 'A');

OUTPUT

MySQL Subquery

The above image shows the output of the subquery with the "IN" operator. It shows the information on the table for the "A" grade.

5) Example: The subquery with the "NOT IN" operator example shows below.

The below subquery uses the "NOT IN" operator, and the condition applies to the "grade" column.

mysql> select topic, points, grade AS remark from student_data  where grade NOT IN (select grade from student_data  where grade = 'A');

OUTPUT

MySQL Subquery

The above image shows the output of the subquery with the "NOT IN" operator. It shows the information of a table except for the "A" grade. The "NOT IN" operator is the inverse of the "IN" operator that shows the exact opposite of the "IN" operator's output.

6) Example: The subquery with the "EXISTS" operator example shows below.

Execute the following query to understand the subquery and its function. The "exists" operator uses with where clause. The inner query applies conditions on the grade column.

mysql> select topic, points, grade AS remark from student_data  where grade NOT IN (select grade from student_data  where grade = 'A');

OUTPUT

MySQL Subquery

The above output image shows existing information in the table. The subquery works with similar or different tables. You can use either EXISTS or NOT EXISTS operator with MySQL subquery. The table shows grade, points, and topic columns.

7) Example: The subquery with "ANY" operator example shows below.

Execute the following subquery to understand the "ANY" operator with the WHERE clause. The inner query applies conditions on the grade column. The grade column works on the "OR" logical condition.

mysql> select topic, points, grade AS remark from student_data  where grade > ANY  (select grade from student_data  where grade = 'A' OR grade ='B');

OUTPUT

MySQL Subquery

Executing the query will return the grades, points, and topic columns. The query uses "greater than" condition before "OR" logical condition.

8) Example: The subquery with the "ALL" operator example shows below.

Execute the following query to understand the subquery and its function. The "ALL" operator is used with the WHERE clause. The inner query applies conditions on the grade column. The grade column works on the "OR" logical condition.

mysql> select topic, points, grade AS remark from student_data  where grade > ALL  (select grade from student_data  where grade = 'A' OR grade ='B');

OUTPUT

MySQL Subquery

Executing the query will return the grades, points, and topic columns. The query uses "greater than" condition before "OR" logical condition.

 Advantages of the "MySQL subquery."

  • The subquery is used to avoid complicated operations of the MySQL system.
  • The subqueries make the queries in a structured form that allows us to isolate each part of a statement.
  • The subqueries provide alternative ways to query the data from the table; otherwise, we need to use complex joins and unions.
  • The subqueries are more readable than complex join or union statements.
  • It allows to work with two expressions efficiently in a single query with multiple conditions.

Disadvantages of the "MySQL subquery."

  • The subquery uses multiple conditions, clauses and operators in a single expression.
  • Sometimes, the data condition is hard to understand in a single query. The large data set becomes confusing and complicated for the subquery.
  • The inner query does not set multiple columns; otherwise, it shows an error.
  • The many operators like "order by" do not apply to the subquery.

Related Topics

MySQL DELETE JOIN

Sometimes join becomes complicated and unwanted. In such cases, we can delete the unwanted joins in the tables. You can delete inner join, left join, right join as per requirement....

3 minutes read.

MySQL RAND() function

In this context, we will learn how we can use the MySQL RAND() function with proper syntax and good examples. Introduction of MySQL RAND() function Basically, in MySQL the RAND() function is...

3 minutes read.

MySQL Union

MySQL contains the same categories of data in a different table. Sometimes, you require multiple tables to collect data together. MySQL union is a function to combine two table's data...

5 minutes read.

MySQL EQUI JOIN

An equijoin is an operation that combines multiple tables based on equality or matching column values in the associated tables. This operation links more than two tables based on a...

5 minutes read.

MySQL PERIOD_DIFF() function

In this context, we will learn how we can use the MySQL PERIOD_DIFF() function with proper syntax and good examples. Introduction of MySQL PERIOD_DIFF() function This function in MySQL is used to...

2 minutes read.

MySQL Composite Index

Composite Index A composite index is an index that is used on multiple columns. MySQL management system manages multiple columns simultaneously. Therefore, the single index contains multiple columns in one query...

3 minutes read.

MySQL LN() function

In this context, we will learn how we can use the MySQL LN() function with proper syntax and good examples. Introduction of MySQL LN() function For evaluation of the natural logarithm of...

2 minutes read.

MySQL DATE_ADD() function

In this context, we will learn how we can use the MySQL DATE_ADD() function with proper syntax and good examples. Introduction of MySQL DATE_ADD() function In MySQL, the DATE_ADD() function is used...

3 minutes read.

MySQL CURRENT_DATE() Function

In this context, we will learn how we can use the MySQL CURRENT_DATE() function to get the current date, and we will see in the two formats, such as strings...

2 minutes read.

MySQL ASIN() Function

In this context we will learn how we can use the ASIN() function in MySQL with proper syntax and good example. Introduction of MySQL ASIN() function The arc sine value of a...

2 minutes read.

MySQL RPAD() function

In this context, we will learn how we can use the MySQL RPAD() function with proper syntax and good examples. Introduction of MySQL RPAD() function RPAD() function in MySQL is used to...

2 minutes read.

MySQL Constraints

MySQL Constraints Introduction The constraints help to restrict what values should be stored in a table. The constraints provide limitations of the columns or data. This function helps to insert data in...

20 minutes read.

MySQL PERIOD_ADD() function

In this context, we will learn how we can use the MySQL PERIOD_ADD() function with proper syntax and good examples. Introduction of MySQL PERIOD_ADD() function In MySQL, PERIOD_ADD() function will help to...

2 minutes read.

MySQL FIND_IN_SET() function

In this context, we will learn how we can use the MySQL MOD() function with proper syntax and good examples. Introduction of MySQL MOD() function MySQL provides a built-in string function called...

4 minutes read.

MySQL LOCATE() function

In this context, we will learn how we can use the MySQL LOCATE() function with proper syntax and good examples. Introduction of MySQL LOCATE() function LOCATE() function in MySQL is used for...

3 minutes read.

MySQL Database Introduction

MySQL - Database Introduction: The database plays an essential role in MySQL for storing and modifying the data. The database creates and keeps multiple tables. The database organizes and manipulates...

8 minutes read.

MySQL Aggregate function

The aggregation function works on the multiple values and returns single value output.  It makes the advanced and complex operation simple. The summation (SUM), MAX, AVG, MIN, COUNT functions are...

5 minutes read.

MySQL RADIANS() function

In this context, we will learn how we can use the MySQL RADIANS() function with proper syntax and good examples. Introduction of MySQL RADIANS() function RADIANS() function in MySQL is used to...

2 minutes read.

MySQL RIGHT() Function

In this context, we will learn how we can use the MySQL RIGHT() Function with proper syntax and good examples. Introduction of MySQL RIGHT() function The RIGHT() function in MySQL is used...

3 minutes read.

MySQL DATE_FORMAT() function

In this context, we will learn how we can use the MySQL DATE_FORMAT() function with proper syntax and good examples. Introduction of MySQL DATE_FORMAT() function DATE_FORMAT() function in MySQL is used to...

3 minutes read.