Why Can I Use Aggregate Functions Without GROUP BY in MySQL?

MySQL's Unique Treatment of Aggregate Functions without GROUP BY

In MySQL, unlike other relational database management systems, it is possible to use aggregate functions in the SELECT list without specifying a GROUP BY clause. This behavior has raised questions among developers familiar with more restrictive DBMS platforms.

The MySQL Rationale

MySQL allows this flexibility by design, considering it a valuable extension to the SQL standard. When an aggregate function is used in the absence of a GROUP BY, the returned result represents a single row containing the aggregate value computed over the entire table. This can be useful in scenarios where summary information is desired without the need for grouping.

Example

For instance, consider the query:

SELECT col1, col2, SUM(col3) FROM tbl1;

Without a GROUP BY, MySQL interprets this as a single group and provides the following output:

col1    col2    SUM(col3)
-------------------------
(First row value of col1)    (First row value of col2)    (Sum of all col3 values)

Limitations with ONLY_FULL_GROUP_BY

MySQL's lenient approach to handling aggregate functions without GROUP BY was introduced with ONLY_FULL_GROUP_BY set to OFF. With the release of MySQL version 5.7.5, ONLY_FULL_GROUP_BY is enabled by default, which restricts this behavior.

Enabling ONLY_FULL_GROUP_BY ensures that aggregate functions can only be used in conjunction with a GROUP BY clause, resulting in an error if a query without it is encountered. This change aligns with the SQL standard and promotes data consistency.

The above is the detailed content of Why Can I Use Aggregate Functions Without GROUP BY in MySQL?. For more information, please follow other related articles on the PHP Chinese website!