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 CEIL() function

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

3 minutes read.

MySQL CROSS JOIN

MySQL CROSS JOIN combines all possibilities of the two or more tables and returns the result that contains every row from all contributing tables. It links several columns from the...

4 minutes read.

MySQL UPDATE JOIN

The UPDATE JOIN is a MySQL statement used to perform cross-table updates that means we can update one table using another table with the JOIN clause condition. The "update join"...

5 minutes read.

MySQL INNER JOIN

MySQL inner join connects two tables by using their common columns. It is the basic join of the MySQL system. It is used to returns only those results from the...

4 minutes read.

MySQL RIGHT JOIN

MySQL right join links two tables with each other. The left table column connects with the complete right side table. Each row of the right table tries to connect with...

4 minutes read.

MySQL ELT() function

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

2 minutes read.

MySQL RTRIM() function

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

2 minutes read.

MySQL GREATEST function

This statement displays the greatest values of the table. The GREATEST function returns a null value when the table contains a null value. The GREATEST function needs a minimum of...

2 minutes read.

MySQL Unique index

It helps to maintain data integrity to enforce the uniqueness of values in one or more columns. We can create more than one UNIQUE index in a single table, which...

3 minutes read.

MySQL MOD() 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 Basically, in MySQL, the MOD() function is...

3 minutes read.

MySQL Table

Introduction MySQL table is an essential part of the system. MySQL table stores data using index, rows, and columns. It accesses data from the server quickly because of the table—this table...

22 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 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 FROM_BASE64() function

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

2 minutes read.

MySQL POWER() function

In this context, we will learn how we can use the MySQL POWER() function with proper syntax and good examples. Introduction of MySQL POWER() function The value of a number raised to...

3 minutes read.

MySQL HEX() function

In this context, we will learn how we can use the MySQL HEX() function with proper syntax and good examples. Introduction of MySQL HEX() function For returning an equivalent hexadecimal string value...

3 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 FORMAT() function

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

2 minutes read.

MySQL Operators

MySQL Operators MySQL operator needs advanced operation on the table and its data. This operator works with the "WHERE" clause. MySQL operators are a statement to modify information. It helps to...

13 minutes read.

MySQL variable

The MySQL variable is essential for storing data in the table. The variable declares data with a specific name or label to avoid confusion. This data label is used in...

6 minutes read.