Přejít na menu

Aggregation vs HAVING/aggregate functions behavior in MySQL

Správa článků

Vyhledávání Vyhledávání
16.8.2017 13:37
,
Počet přečtení: 181
When and why MySQL aggregates rows.

MySQL aggregates data when...

  • there is GROUP BY clause
  • there is some aggregate function (like MAX(), ...)

It does not aggregate when there is HAVING clause without GROUP BY or aggregating function

Examples

— does not work - invalid use of aggregate function
SELECT * FROM teacher WHERE age = MAX(age)

— works well (subquery return one result which is used for outer select)
SELECT * FROM teacher WHERE age = (SELECT MAX(age) FROM teacher)
SELECT * FROM teacher HAVING age = (SELECT MAX(age) FROM teacher)

— works but PROBABLY returns the empty result (MAX causes aggregation of the whole query and random row is picked for the age value, which probably will not match the MAX age value)
SELECT * FROM teacher HAVING age = MAX(age)

Vytvořil 18. srpna 2017 v 08:12:44 mira. Upravováno 2x, naposledy 30. září 2017 v 19:17:29, mira


Diskuze ke článku

Vložení nového komentáře
*
*
*