SELECT year, COUNT(*) sales, SUM(price) income FROM sales HAVING sales > 10 AND income < 1000;
SELECT *
FROM (SELECT year, COUNT(*) as nb_sales, SUM(price) as income
FROM sales)
WHERE nb_sales > 10 AND income < 1000;
There may also be performance differences depending on the rest of the query, but for simple examples like this exactly the same plan will be used, so the performance will be identical.
Unfortunately, the simplest examples do not always illustrate the potential benefits of less common syntax.
> Subselects can't do something like: (…)
which is wrong.
SELECT year, sales, income FROM ( SELECT year, COUNT(*) sales, SUM(price) income FROM sales ) AS innerquery WHERE sales > 10 AND income < 1000;