How can we use a subquery that contains a reference to a table that also appears in the outer query?

MySQLMySQLi Database

A subquery that contains a reference to a table that also appears in the outer query is called a correlated subquery. In this case, MySQL evaluates from inner query to the outer query. To understand it we are having the following data from table ‘cars’ −

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)

Followings are two MySQL queries having subqueries that contain a reference to a table i.e. ‘Cars’ that also appears in the outer query.

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)

In the above query, MySQL evaluates from inner query i.e. first it evaluates ‘Select AVG(Price) from Cars’ the inner query and then evaluates the outer query ‘Select Name from Cars Where Price <’. Similarly, MySQL evaluates in the query below.

mysql> Select Name from cars WHERE Price > (SELECT AVG(Price) from Cars);
+--------+
| Name   |
+--------+
| BMW    |
| VOLVO  |
| Toyota |
+--------+
3 rows in set (0.00 sec)
raja
Published on 21-Feb-2018 13:21:28
Advertisements