Why Does MySQL Allow Selecting Columns Not in the GROUP BY Clause?

Selecting Columns Not in the GROUP BY Clause: MySQL's Ambiguous Behavior

In MySQL, it is possible to select a column not included in an aggregate function or the GROUP BY clause, which can be confusing for programmers coming from other SQL databases. This behavior deviates from the ANSI SQL standard, which requires all selected columns to be part of the grouping or appear in an aggregate function.

Ambiguous Queries and Arbitrary Value Selection

When MySQL encounters such a query, it silently returns an arbitrary value from the column for each group, typically the value from the first or last row in the group. This behavior can lead to ambiguous results, especially if the values in the non-grouped column are not consistent within each group.

Example Query

Consider the following MySQL query:

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;

In this query, col4 is not part of the GROUP BY clause or an aggregate function. MySQL will arbitrarily select a value for col4 for each group. This can be problematic if different rows within a group have different values for col4.

Functional Dependency and Deterministic Results

MySQL allows selecting non-grouped columns when they are functionally dependent on the column(s) in the GROUP BY clause. This means that for each value of the grouping column(s), there is only one possible value for the non-grouped column. In such cases, the query will return a deterministic result.

However, it is important to note that MySQL's behavior with ambiguous queries can change in future versions. Therefore, it is recommended to avoid using non-grouped columns in SELECT clauses unless they are explicitly included in the GROUP BY clause or an aggregate function.

"ONLY_FULL_GROUP_BY" SQL Mode

MySQL 5.7 introduced the "ONLY_FULL_GROUP_BY" SQL mode, which enforces the ANSI SQL standard for GROUP BY queries. When this mode is enabled, selecting non-grouped columns without an aggregate function will result in an error.

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