Previous read Why Include Non-Aggregated Columns in GROUP BY

In MySQL, the handling of the GROUP BY clause can differ based on the SQL mode settings, particularly the ONLY_FULL_GROUP_BY mode. The behavior you mentioned, where all selected columns in the SELECT clause that are not under an aggregate function must be included in the GROUP BY clause, aligns with the SQL standard and is enforced when ONLY_FULL_GROUP_BY is enabled in MySQL.

Understanding MySQL GROUP BY Behaviors

1. With ONLY_FULL_GROUP_BY Enabled:

   • This mode enforces standard SQL behavior where every column in the SELECT list that is not an aggregate function must appear in the GROUP BY clause, or must be functionally dependent on the columns in GROUP BY.

   • When enabled, queries that do not adhere to this standard will result in an error. This mode is part of the default SQL mode from MySQL 5.7 onwards and helps prevent ambiguous query results.

2. Without ONLY_FULL_GROUP_BY:

   • If this mode is disabled, MySQL allows more flexibility. You can include columns in the SELECT list that are not in the GROUP BY clause and are not aggregated. MySQL will return a value for these columns from one of the rows in the group, but this value is nondeterministic unless all rows in the group are the same for this column.

   • This behavior can lead to potentially confusing and misleading results, as the returned row for non-aggregated columns is not specified and can vary depending on factors like index structure or the query execution plan.

Example to Illustrate Both Modes

Let's consider a table Employees with columns Department, EmployeeName, and Salary.

SELECT Department, EmployeeName, AVG(Salary)
FROM Employees
GROUP BY Department;

• With ONLY_FULL_GROUP_BY Enabled: This query will result in an error because EmployeeName is neither included in an aggregate function nor listed in the GROUP BY clause.

• Without ONLY_FULL_GROUP_BY: MySQL might execute this query successfully, picking an arbitrary EmployeeName from each department group for the output, which can be misleading because it suggests a relationship between the chosen employee name and the average salary that does not actually exist.

Best Practices and Recommendations

• Enable ONLY_FULL_GROUP_BY: It's generally a good practice to enable this mode to ensure your SQL queries are portable and adhere to the standard SQL behavior, preventing unexpected and ambiguous results.

• Check SQL Mode: To see whether ONLY_FULL_GROUP_BY is enabled in your MySQL server, you can use the following SQL command:

    SHOW VARIABLES LIKE 'sql_mode';
  

• Update Legacy Queries: If you are working on a MySQL server where ONLY_FULL_GROUP_BY is not enabled and you're planning to enable it, make sure to update legacy queries to comply with this setting to avoid runtime errors.

By maintaining the discipline enforced by ONLY_FULL_GROUP_BY, you can ensure more predictable and reliable behavior from your SQL queries in MySQL.