0

I just noticed that if I do select * from the tableName group by columnName; then it does not result in error. Also, even if I select the non-grouping column.

My understanding was that we can select only grouped columns and aggregated columns.

Why is this allowed? Does anything special behind the scene?

here is the SQL fiddle http://sqlfiddle.com/#!9/d3a23/5

Ajinkya Pisal
  • 551
  • 1
  • 6
  • 24
  • 1
    Darn it, when I had my answer ready, this question was already blocked and marked as a duplicate. So in short: The SQL standard requires all selected fields either to be in the group by clause or be functionally dependent on it or be aggregates. This "functionally dependent " thing is hard to determine for a DBMS, so many forbid these fields in the select clause (which often makes their queries quite clumsy). MySQL instead allows any field, not checking functional dependency. When a field is not, one value gets picked arbitrarily. – Thorsten Kettner Apr 05 '16 at 20:45
  • This is a (mis)feature of MySQL, that you can disable with the right parameter setting. – Gordon Linoff Apr 05 '16 at 21:05
  • @ThorstenKettner thanks. It makes sense – Ajinkya Pisal Apr 06 '16 at 07:20

2 Answers2

1

MySQL doesn't enforce this particular syntax, but you need to understand the effects that it has when you leave columns in your query that aren't part of the GROUP BY.

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • yes. I am aware of the effect. But wanted to understand why this functionality was kept if it seems kind of wrong? – Ajinkya Pisal Apr 05 '16 at 20:30
  • 2
    DBMS that only allow for grouped columns and aggregated columns only comply with the old ANSI Standard 1992, and not with the later Standards 1999 and 2003. MySQL admits not to be compliant and offers us a comfortable way per default and the safer way via ONLY_FULL_GROUP_BY setting. I think this is a rather good compromise – Thorsten Kettner Apr 05 '16 at 21:15
1

It's because of the extension provided by MySQL. Per MySQL documentation

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.

Rahul
  • 76,197
  • 13
  • 71
  • 125