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.