Why Does MySQL Allow Non-Grouped Columns in GROUP BY Queries?

MySQL's Non-Standard GROUP BY Extension: Allowing Non-Grouped Column Selection

The standard SQL syntax prohibits selecting non-aggregate fields that are not explicitly grouped in the GROUP BY clause of an aggregate query. However, MySQL deviates from this standard by allowing such selections.

Standard SQL vs. MySQL's Extension

Until 1992, the standard SQL specification prohibited this behavior, ensuring that aggregate queries only returned aggregated values or columns grouped in the GROUP BY clause.

However, with the release of SQL-2003, the standard was amended to allow the selection of columns that are functionally dependent on the grouping columns. MySQL's extension, however, goes beyond this standard by permitting all columns to be selected, regardless of their functional dependencies.

Implications of MySQL's Extension

This extension has several implications:

• Performance Improvement: Avoiding unnecessary column sorting and grouping can significantly improve query performance.
• Maintainability: Allowing non-grouped column selection simplifies the writing and maintenance of complex queries.
• Indeterminate Results: However, this extension introduces a potential for indeterminate results if the non-grouped columns contain different values within each group.

MySQL's Motivation for the Extension

MySQL implemented this extension to comply with SQL-2003 standards, while also addressing the performance and maintainability concerns mentioned above. However, they chose a simplified approach by allowing all columns to be selected, rather than implementing a more complex mechanism to identify functionally dependent columns.

Disabling the Extension

If desired, users can disable the extension by setting the sql_mode to ONLY_FULL_GROUP_BY. This will restore the stricter SQL-92 behavior, ensuring that non-grouped columns cannot be selected in aggregate queries.

Recent Developments

In 2011, PostgreSQL added a more restrictive implementation of this feature, closer to the SQL standard. MySQL's handling of GROUP BY was further improved in version 5.7 (2015), incorporating a mechanism to recognize functional dependencies, aligning it more closely with the standard.

The above is the detailed content of Why Does MySQL Allow Non-Grouped Columns in GROUP BY Queries?. For more information, please follow other related articles on the PHP Chinese website!