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

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)

Alankritha Ammu

Published on 21-Feb-2018 13:21:28

• Related Questions & Answers
• How can we nest a subquery within another subquery?
• How can we use a MySQL subquery with INSERT statement?
• How can we use a MySQL subquery with FROM clause?
• How MySQL evaluates if we use EXISTS operator with a subquery that returns no rows?
• How can I use MySQL subquery as a table in FROM clause?
• How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?
• How can we create a MySQL stored function that uses the dynamic data from a table?
• How can we create a MySQL view with a subquery?
• Selecting a column that is also a keyword in MySQL?
• How to create a frequency table of a vector that contains repeated values in R?
• How can we use a MySQL stored function in a database query?
• Can we assign a reference to a variable in Python?
• Can we use {} while creating a MySQL table?
• Can we select field name in MySQL that contains an asterisk?
• Can we use the word user for a MySQL table?