What are MySQL subqueries and its general categories?

A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at runtime. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses.

A subquery can be used any place where an expression is allowed providing it returns a single value. This means that a subquery that returns a single value can also be listed as an object in a FROM clause listing. This is termed an inline view because when a subquery is used as part of a FROM clause, it is treated like a virtual table or view. A subquery can be placed either in FROM clause, WHERE clause or HAVING clause of the main query. It is also called an INNER QUERY or INNER SELECT and the query that contains subquery is called OUTER QUERY or OUTER SELECT or CONTAINER QUERY. Followings are its general categories −

Scalar Subquery

Scalar subqueries return a single value i.e. one row with one column of data. A scalar subquery is a simple operand and we can use it almost anywhere a single column or literal is legal. To illustrate it we are using the tables ‘Cars’, ‘Customers’ and ‘Reservations’ having the following data −

mysql> Select * from Cars;
+------+--------------+---------+
| ID   | Name         | Price   |
+------+--------------+---------+
| 1    | Nexa         | 750000  |
| 2    | Maruti Swift | 450000  |
| 3    | BMW          | 4450000 |
| 4    | VOLVO        | 2250000 |
| 5    | Alto         | 250000  |
| 6    | Skoda        | 1250000 |
| 7    | Toyota       | 2400000 |
| 8    | Ford         | 1100000 |
+------+--------------+---------+
8 rows in set (0.02 sec)

mysql> Select * from Customers;
+-------------+----------+
| Customer_Id | Name     |
+-------------+----------+
| 1           | Rahul    |
| 2           | Yashpal  |
| 3           | Gaurav   |
| 4           | Virender |
+-------------+----------+
4 rows in set (0.00 sec)

mysql> Select * from Reservations;
+------+-------------+------------+
| ID   | Customer_id | Day        |
+------+-------------+------------+
| 1    | 1           | 2017-12-30 |
| 2    | 2           | 2017-12-28 |
| 3    | 2           | 2017-12-29 |
| 4    | 1           | 2017-12-25 |
| 5    | 3           | 2017-12-26 |
+------+-------------+------------+
5 rows in set (0.00 sec)

As we know that the scalar subquery will return a single value, the following is a scalar subquery −

mysql> Select Name from Customers WHERE Customer_id = (Select Customer_id FROM Reservations WHERE ID = 5);
+--------+
| Name   |
+--------+
| Gaurav |
+--------+
1 row in set (0.06 sec)

Table Subquery

A table subquery returns a result with one or more rows containing one or more columns of data. The following query using the data from tables ‘cars’, ‘Customers’ and ‘Reservations’ is an example of table subquery −

mysql> Select Name from customers where Customer_id IN (SELECT DISTINCT Customer_id from reservations);
+---------+
| Name    |
+---------+
| Rahul   |
| Yashpal |
| Gaurav  |
+---------+
3 rows in set (0.05 sec)

Correlated Subquery

A correlated subquery is a subquery that uses values from the outer query in its WHERE clause. The following query using the data from tables ‘cars’ is an example of a correlated subquery −

mysql> Select Name from cars WHERE Price < (SELECT AVG(Price) from Cars);
+--------------+
| Name         |
+--------------+
| Nexa         |
| Maruti Swift |
| Alto         |
| Skoda        |
| Ford         |
+--------------+
5 rows in set (0.00 sec)

mysql> Select Name from cars WHERE Price > (SELECT AVG(Price) from Cars);
+--------+
| Name   |
+--------+
| BMW    |
| VOLVO  |
| Toyota |
+--------+
3 rows in set (0.00 sec)