Скалярные подзапросы

Скалярный подзапрос возвращает одно значение

Скалярный подзапрос должен дать результат, который можно использовать как обычное одиночное значение: одно значение одного столбца.

SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) AS average_price
FROM products;

Вложенный запрос вычисляет одно среднее значение. Поэтому его можно поместить прямо в список SELECT и вывести рядом с данными каждой строки.

Форма результата должна соответствовать контексту

Если место в запросе ожидает одно значение, а подзапрос фактически возвращает несколько строк, СУБД не сможет использовать его как скалярное выражение и обычно завершит запрос ошибкой.

SELECT name
FROM products
WHERE price = (
    SELECT MAX(price)
    FROM products
);

MAX(price) гарантирует одно агрегированное значение, поэтому такая форма естественно подходит для сравнения через =.