SELECT id, firstname, lastname, weight,
FROM person sq -- 'sq' is an arbitrary alias name for the table in the subquery
WHERE sq.lastname = p.lastname -- identify the inner and outer table by its alias names
) family_average -- an arbitrary alias name for the computed family average
FROM person p -- 'p' is an arbitrary alias name for the table in the surrounding query
ORDER BY lastname, weight;
The subselect obtains one row of the surrounding SELECT after the next as an parameter with the name 'p'.
Within the subselect all columns of the row 'p' are known and may be used.
Here the family name from the outside row is used in the subquery to find all persons within the family and the average weight of the family members.
Subselect queries are executed once per row of the surrounding query. Therefore they are much more costly than non-correlated subqueries. There might exist an equivalent solution using JOIN or GROUP BY which works with better performance. The query optimizer of the DBMS internally might rearrange the given subquery into one of the equivalent forms. But this does not work in all cases. The distinction between correlated and non-correlated subqueries is universal. It applies also to the other subquery classes.