MySQL Subquery Categories
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 contains references to the columns in the outer query and cannot be evaluated independently of it.
Non-correlated subquery contains no references to the outer query, is not dependent on it and can be evaluated as a completely separate query.
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:
|Scalar||Single row with a single column|
|Single column with one or more row|
|Row||Single row with one or more columns|
|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.
MySQL Tutorials on this website:
For more information on MySQL Database: