MySQL Subquery Categories

Overview

In this tutorial, we will learn about MySQL Subquery Categories.We can categorize subqueries into many types.

Based on outer/inner query dependency
Based on data returned by the subquery.

There are two categories of subqueries based on the interaction between the outer and inner query. They are as follows:

Correlated Subquery
Non-correlated Subquery

Correlated Subquery

Correlated subquery contains references to the columns in the outer query and cannot be evaluated independently of it.

Non-Correlated Subquery

Non-correlated subquery contains no references to the outer query, is not dependent on it and can be evaluated as a completely separate query.

https://www.testingdocs.com/mysql-non-correlated-subquery/

Types of Subquery

We can categorize subqueries based on the return data. The type of table returned by the subquery determines how it may be used and which operators may be used by the outer query to interact with the table returned by the subquery.

Subqueries can return different amounts and types of table data as follows:

Type	Definition
Scalar	Single row with a single column
Column	Single column with one or more row
Row	Single row with one or more columns
Table	One or more row with one or more column

When the outer query has finished executing, the table returned by any subqueries are discarded, making a subquery act like a temporary table with statement scope. That means the MySQL database server frees up any storage allocated to the subquery results after the statement has finished execution.

—