Is Using HAVING Without GROUP BY Standard SQL?

HAVING Without GROUP BY: Standard SQL Conformance

The query structure presented in the question:

SELECT *
FROM Book
HAVING NumberOfPages = MAX(NumberOfPages)

raises concerns regarding its validity according to standard SQL. The query utilizes a HAVING clause without a corresponding GROUP BY clause.

Standard SQL defines that a HAVING clause is valid if its search conditions satisfy specific criteria. These criteria include being functionally dependent on the columns referenced in the GROUP BY clause or being outer references.

In the given query, there is no GROUP BY clause. Therefore, the search condition NumberOfPages = MAX(NumberOfPages) does not meet the defined criteria, as there is no unambiguous functional dependency of NumberOfPages on any specific group.

To conform to the standard, the query would need to be modified to include a GROUP BY clause, such as:

SELECT *
FROM Book
GROUP BY BookId
HAVING NumberOfPages = MAX(NumberOfPages)

In this revised query, the GROUP BY clause partitions the data by BookId, creating distinct groups for each book. The HAVING clause now becomes valid, as it operates on these groups, checking if any group contains a row with the maximum NumberOfPages.

It's important to note that some database systems, such as MySQL, may allow such queries without a GROUP BY clause, but this behavior is considered a non-standard extension.

The above is the detailed content of Is Using HAVING Without GROUP BY Standard SQL?. For more information, please follow other related articles on the PHP Chinese website!