MySQL- what does the WHERE clause do in the correlated subquery here

Question

Quoting from here :

"In the following query, we select products whose buy prices are greater than the average buy price of all products in each product line.

SELECT 
    productname, 
    buyprice
FROM
    products p1
WHERE
    buyprice > (SELECT 
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline)

"

You can find the executable code in an editor here . If I just omit the WHERE clause, the result seems to be same except the last 2 rows do not appear in the output result.

What do the left and right sides of the WHERE clause exactly do here i.e. what values they get each time, how the comparison takes place etc ?

Answer 1

This query return productnames which have a buyprice greater than the average of buyprice of all products on the same productline.

SELECT 
    productname, 
    buyprice
FROM
    products p1
WHERE
    buyprice > (
        SELECT AVG(buyprice) --return the average buyprice of all products
        FROM products
        WHERE productline = p1.productline  -- same productline
    )