Can a HAVING Clause Exist Without a GROUP BY Clause in Standard SQL?

HAVING without GROUP BY: Standard SQL Compliance

Can a HAVING clause exist without a GROUP BY clause in standard SQL? According to the standard, a GROUP BY clause is required when using a HAVING clause. The standard states that each column referenced in the HAVING clause must be either functionally dependent on the columns in the GROUP BY clause or an outer reference.

However, MySQL allows a HAVING clause without a GROUP BY clause, and it functions as expected under a specific condition: when the first row has the maximum value for the specified column.

To conform to the standard, the query should be modified to include a GROUP BY clause. For example, if we want to retrieve the rows with the maximum number of pages, the corrected query would be:

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

This query would group the rows by BookID and apply the HAVING condition to each group, returning only the rows with the maximum number of pages in each group.

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