Why Can MySQL Aggregate Functions Be Used Without a GROUP BY Clause?

MySQL Aggregate Functions: The GROUP BY Quandary

In MySQL, aggregate functions can be used in a SELECT statement without explicitly defining a GROUP BY clause, while other database systems like SQL Server may throw an error. This behavior raises questions about why MySQL allows such queries to execute.

The MySQL Design

MySQL permits aggregate functions in the SELECT list without a GROUP BY clause as an extension to the SQL standard. Without grouping, the database assumes a single indeterminate group, and any name value can be chosen for that group. This behavior allows for easier aggregation of data across an entire table.

Example Query

Consider the query:

SELECT name, MAX(age) FROM t;

Without a GROUP BY clause, MySQL returns a single row with the first name value encountered and the maximum age value from the entire table.

ONLY_FULL_GROUP_BY Setting

MySQL version 5.7.5 and later introduced the ONLY_FULL_GROUP_BY setting, which controls the handling of aggregate functions without a GROUP BY clause. Enabling this setting requires the presence of a GROUP BY clause for any aggregate function usage.

Conclusion

MySQL's behavior of allowing aggregate functions without a GROUP BY clause provides flexibility for aggregating data across an entire table. However, the ONLY_FULL_GROUP_BY setting can enforce stricter adherence to the SQL standard for more controlled aggregation.

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