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

MySQL: Understanding the Allowance of Selecting Non-Aggregated Columns in GROUP BY

In MySQL, you may encounter scenarios where a view like the following is permissible:

SELECT
     AVG(table_name.col1),
     AVG(table_name.col2),
     AVG(table_name.col3),
     table_name.personID,
     table_name.col4
FROM table_name
GROUP BY table_name.personID;

This query includes an aggregate function for col1, col2, and col3, but it also selects col4 without any aggregation. Normally, such a query would be considered invalid in other database systems like Microsoft SQL Server.

In MySQL, however, this behavior is allowed. It's important to understand why:

Arbitrary Value Selection

When selecting non-aggregated columns in a GROUP BY query, MySQL selects an arbitrary value from the group. This typically corresponds to the value from the row stored first in the group.

Ambiguous Queries

This feature can lead to ambiguous queries, where the selected value for the non-aggregated column may vary based on the order of rows in the group. For instance, the query below could return different values for col4 for the same personID depending on row ordering:

SELECT AVG(table1.col1), table1.personID, persons.col4
FROM table1 JOIN persons ON (table1.personID = persons.id)
GROUP BY table1.personID;

Functional Dependency

To avoid ambiguity, it's crucial to select columns that are functionally dependent on the columns in the GROUP BY criteria. This means that there should be only one possible value for the "ambiguous" column for each unique value of the grouping column(s).

ONLY_FULL_GROUP_BY Mode

MySQL provides the ONLY_FULL_GROUP_BY SQL mode, which can be enabled to make MySQL adhere to the ANSI SQL standard. In this mode, selecting non-aggregated columns in a GROUP BY query will result in an error, unless those columns are functionally dependent on the grouping columns.

Conclusion

While MySQL allows selecting non-aggregated columns in GROUP BY queries, it's essential to be aware of the potential for ambiguous results. To ensure deterministic queries, it's recommended to rewrite them to be unambiguously grouped by functionally dependent columns or to enable the ONLY_FULL_GROUP_BY mode.

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