How Does MySQL Handle Non-Aggregated Columns in GROUP BY Clauses?

Non-Aggregated Columns in MySQL GROUP BY: A Deviation from Standard

MySQL's implementation of GROUP BY allows the inclusion of non-aggregated columns in the SELECT list, even when they are not included in the grouping criteria. This behavior differs from the ANSI SQL standard, which prohibits the selection of non-grouped columns in such queries.

This discrepancy arises from the concept of functional dependency. In MySQL, if a non-grouped column is functionally dependent on the grouping column, MySQL silently chooses and returns a single value for that column. However, it's important to note that such queries can lead to ambiguous results if the functional dependency is not enforced.

For instance, the following query would be invalid in ANSI SQL:

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

In MySQL, this query is allowed because persons.col4 is functionally dependent on table1.personID via the join. However, if multiple persons have the same personID, the result for persons.col4 could be arbitrary.

To prevent such ambiguities, MySQL provides the ONLY_FULL_GROUP_BY SQL mode, which enforces stricter grouping rules in line with the ANSI standard. By default, this mode is not enabled, but it can be activated to ensure that queries adhere to the standard behavior.

Note that SQLite also permits non-aggregated columns in GROUP BY queries, but it selects the last value in the group instead of an arbitrary one. This behavior should not be relied upon, as it may change in future versions. To avoid ambiguities, it's recommended to rewrite queries to be unambiguous and deterministic.

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