correlated subquery and GROUP BY

Question

I started learning Mysql, and I have a problem with understanding the correlated subquery, I've search for definitions saying, it executed once for each row, but when it comes to the examples:

DEPOT (N_Dpt, Name_Dpt, City)
STORE (N_Dpt #, N_Product #, Qt_Stored)
PRODUCT (N_Product, Label_Product, Price_U, Weight, Color)  


               

List of highest unit prices for each product type

SELECT Product_Label, Price_U FROM Product AS P1  
WHERE Price = (SELECT MAX (Price_U) FROM PRODUCT P2  
WHERE P2. Product_Label = P1. Product Label )

1st question : why it is a correlated subquery?
2nd question : why can't we use GROUP BY ?

SELECT Product_label, MAX(Price_U) FROM PRODUCT GROUP BY Product_label

Answer 1

Consider the following...

DROP TABLE IF EXISTS product;

CREATE TABLE product
(id SERIAL PRIMARY KEY
,product_label CHAR(1) NOT NULL
,price_u INT NOT NULL
);

INSERT INTO product VALUES
(1,'a',12),
(2,'a',18),
(3,'a',4),
(4,'b',9),
(5,'b',1),
(6,'c',13);


-- Correlated subquery:

SELECT product_label
     , price_u
  FROM product p1  
 WHERE price_u = (SELECT MAX(price_u) FROM product p2 WHERE p2.product_label = p1.product_label);
                                                      -----------------------------------------

 +---------------+---------+
 | product_label | price_u |
 +---------------+---------+
 | a             |      18 |
 | b             |       9 |
 | c             |      13 |
 +---------------+---------+
 
-- Uncorrelated subquery

SELECT x.product_label
     , x.price_u 
  FROM product x
  JOIN 
     ( SELECT product_label
            , MAX(price_u) price_u
         FROM product 
        GROUP
           BY product_label
     ) y
    ON y.product_label = x.product_label
   AND y.price_u = x.price_u;

 +---------------+---------+
 | product_label | price_u |
 +---------------+---------+
 | a             |      18 |
 | b             |       9 |
 | c             |      13 |
 +---------------+---------+
   
-- No subquery

SELECT x.product_label
     , x.price_u 
  FROM product x
  LEFT
  JOIN product y
    ON y.product_label = x.product_label
   AND y.price_u > x.price_u
 WHERE y.id IS NULL;

 +---------------+---------+
 | product_label | price_u |
 +---------------+---------+
 | a             |      18 |
 | b             |       9 |
 | c             |      13 |
 +---------------+---------+

The above is not intended to be exhaustive