← back to the blog


MySQL SubQuery - Correlated SubQuery

Posted on May 25th, 2020 in MySQL by George

 

MySQL subquery is a query nested within another query such as SELECTINSERTUPDATE or DELETE.

A subquery must be closed in parentheses, also it can be nested inside another subquery.

Given the ER Diagram, I will demonstrate an example of a very basic implementation of subquery below: 

 

ER Diagram for example

 

In the following example the subquery will return all office codes of the offices located in London.

The outer query selects the last name and the first name of employees who work in the offices whose office code is matched to the results of the inner query.

 

subquery example

 

Correlated subquery

 

A correlated subquery is a subquery that uses the data from the outer query, depending on it because it is evaluated once for each row in the outer query.

 

Let's dive to an example given the following ER Diagram:

 

 

In this example, we select product, name, line, and price for products where the average price is greater than the buy price for products that have the same product line.

 

 

 

Thank you.