MySQL Subquery

In MySQL, subqueries are a part of it. The queries are defined as queries nested into another SQL query embedded with SELECT, INSERT, UPDATE or DELETE along with some of the preferred operators added in the spice. There is also a possibility that a subquery can be nested with yet another subquery.

It is said that the subquery is known as the inner query as it is nested within the main query, and for that reason, the query which contains the subquery is the outer query. It is good to keep in mind that the inner query is executed and destined to produce a result first during execution. In contrast, the outer query is only executed after the inner query is done.

Syntax

SELECT column_list (s) FROM  table_name  
WHERE  column_name OPERATOR  
   (SELECT column_list (s)  FROM table_name [WHERE])

Example: MySQL Subquery

Let us assume there is a table student, and there are a table personal_details. Let us try to fetch all students whose age is less than or equal to 18.

SELECT 
    lastName, firstName
FROM
    students
WHERE
    rollno IN (SELECT 
            rollno
        FROM
            personal_details
        WHERE
            age <= 18);

This query will return all students whose age is less than or equal to 18, matching the rollno from both tables. The outer query selects the Lastname and first name of the students whose rollno are in the result set returned by the subquery.

The Rules For Subqueries

It is an excellent practice always to use subqueries inside parentheses.
If there are no multiple columns in the main query, the subquery can have only one column in the select command.
The ORDER BY cannot be used in the subquery, whereas it can be used in the main query.
If the subquery is used in a set function, it cannot be immediately enclosed in the set function.

Why Subqueries?

The subqueries make the queries more structured, allowing us to isolate each part of a statement.
The use of complex joins and unions can be minimized when the subqueries are used.
Subqueries are more readable.