Can You Use HAVING Without GROUP BY in SQL?

HAVING Without GROUP BY: An SQL Anomaly

In standard SQL, is it possible to use a HAVING clause without a GROUP BY clause?

Question 1: Standard SQL Compliance

The provided sample query:

SELECT *
FROM Book
HAVING NumberOfPages = MAX(NumberOfPages)

contradicts the SQL standard. According to the standard, all column references in the HAVING clause must reference columns functionally dependent on the set of columns specified in the GROUP BY clause. However, the query in question lacks a GROUP BY clause, making it non-standard.

Question 2: MySQL Compatibility

The query works in MySQL only under specific conditions, where the first row of the result contains the maximum value for NumberOfPages. This behavior is likely due to MySQL's handling of HAVING clauses in the absence of GROUP BY clauses.

Question 3: Standard SQL Justification

The standard prohibits HAVING clauses without GROUP BY clauses because they create ambiguity in specifying which rows satisfy the HAVING condition. For a table expression considered as a whole, there is no logical basis for selecting a specific row for comparison with aggregate functions.

Example of a Valid Standard SQL Query:

This query demonstrates a valid use of a HAVING clause without a GROUP BY clause:

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

The query always returns either a single row with the value 'T' (indicating books with differing numbers of pages) or an empty set (no rows).

Conclusion:

While the sample query works in MySQL, it does not conform to the standard SQL. Standard SQL prevents the use of HAVING clauses without GROUP BY clauses to avoid ambiguity and ensure logical consistency.

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