Can HAVING be Used in SQL Without a GROUP BY Clause?

Can a HAVING Clause Be Used Without a GROUP BY Clause?

The SQL standard outlines specific criteria for using the HAVING clause, which includes referencing columns that are functionally dependent on the GROUP BY clause or outer references. However, the provided query raises the question of whether it is possible to use HAVING without GROUP BY.

Standard SQL Specifications

According to the standard, a HAVING clause without a GROUP BY clause is not explicitly prohibited. However, the query exhibits questionable behavior:

SELECT *
FROM Book
HAVING NumberOfPages = MAX(NumberOfPages)

This query uses an aggregate function (MAX) in the HAVING clause, but it does not indicate which specific row should be selected. Therefore, its behavior is not strictly defined by the standard.

MySQL Implementation

MySQL interprets this query as if there were an implicit GROUP BY clause, selecting only the row with the maximum NumberOfPages value. This behavior is not compliant with the standard.

Reason for Standard Noncompliance

The standard requires the HAVING clause to operate on groups of rows defined by the GROUP BY clause. Its purpose is to filter out groups based on aggregate values. Using HAVING without GROUP BY would allow filtering on arbitrary conditions, potentially leading to unexpected results.

Valid Standard SQL Query

A valid Standard SQL query using HAVING without GROUP BY would be:

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

This query checks if the minimum NumberOfPages value is less than the maximum value, returning a single row with the result 'T' if true and an empty set if false. This behavior is consistent with the standard's requirement that HAVING applies to aggregate functions operating on the entire table.

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