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

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

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

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

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

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

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

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

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.