Why Can MySQL Use Aggregate Functions Without a GROUP BY Clause?

MySQL Aggregate Functions: Why No GROUP BY Clause Required?

In MySQL, aggregate functions can be used in the SELECT list without an accompanying GROUP BY clause, unlike other RDBMS products such as SQL Server. This behavior is intentional, providing a unique extension to the standard.

For instance, the query "SELECT col1,col2,sum(col3) FROM tbl1;" will execute successfully, returning a single row containing the first row values for col1 and col2 and the sum of all col3 values.

Explanation

MySQL permits this behavior because it considers that without a GROUP BY clause, all rows are implicitly grouped together. Consequently, an aggregate function applied to an entire table or multiple rows operates on a single, implicit group.

Previous Behavior

Prior to MySQL version 5.7.5, this behavior was not subjected to any restrictions. However, a new server SQL mode, ONLY_FULL_GROUP_BY, has been introduced to enforce stricter adherence to the SQL standard. When enabled, this mode requires a GROUP BY clause for all aggregate function usage.

Conclusion

MySQL's unique handling of aggregate functions without a GROUP BY clause offers flexibility in data retrieval. However, it's essential to understand this behavior and consider enabling ONLY_FULL_GROUP_BY to ensure compliance with SQL standards.

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