Can a HAVING Clause Be Used Without a GROUP BY Clause in Standard SQL?

HAVING Without GROUP BY

According to the SQL standard, a HAVING clause is typically used in conjunction with a GROUP BY clause to filter groups of rows based on aggregate calculations performed on those groups. However, some database systems allow a HAVING clause to be used without GROUP BY.

Question:

Is it possible to use a HAVING clause without GROUP BY according to the SQL standard?

Answer:

Yes, it is possible to use a HAVING clause without GROUP BY in Standard SQL. However, the behavior of such queries varies depending on the database system in use.

MySQL Behavior:

In MySQL, a HAVING clause without GROUP BY is allowed, provided that the first row of the result set has the maximum value for the aggregated column. This behavior departs from the SQL standard, which generally requires a GROUP BY clause in the presence of a HAVING clause.

SQL Standard Interpretation:

The SQL standard defines a HAVING clause as acting upon aggregates calculated over the result set produced by a GROUP BY clause. Without a GROUP BY clause, it becomes unclear which rows the HAVING clause refers to.

Therefore, according to the SQL standard, a HAVING clause without GROUP BY is not strictly valid. However, some database systems, such as MySQL, may allow such queries for compatibility reasons.

Valid Standard SQL Example:

SELECT 'T' AS result
FROM Book
HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

This query is valid Standard SQL and will return a single row with a column containing the value 'T' if there are books with different numbers of pages.

Conclusion:

While using a HAVING clause without GROUP BY may be convenient in certain scenarios, it is important to note that the behavior of such queries is not standardized and may vary between database systems. For reliable cross-database compatibility, it is recommended to use GROUP BY in conjunction with HAVING when filtering aggregate calculations.

The above is the detailed content of Can a HAVING Clause Be Used Without a GROUP BY Clause in Standard SQL?. For more information, please follow other related articles on the PHP Chinese website!