Why are Mysql Group By and Oracle Group different types of behavior

Question

Why Mysql Group By and Oracle Group are Different by Species

I have found many times that the Mysql groupBy functionality and the Oracle GroupBy functionality behave differently

Many times I found an error in Oracle (this is really a wrong request), but Mysql will give the result to this

is there any reason for this strange mysql behavior

Answer 1

MySQL designers made a custom extension GROUP BYto make the development process easier and some queries more efficient.

Here is their rationale.

http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html

ONLY_FULL_GROUP_BY, . , .

 SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'  

, , SQL, ; .

Answer 2

Oracle SQL, , , , group by.

MySQL Docs:

---

SQL , GROUP BY, , GROUP BY. , SQL, GROUP BY:

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

, GROUP BY.

MySQL GROUP BY, , GROUP BY. , MySQL. , . , , , GROUP BY, .

---

, , MySQL , :

, . , , , GROUP BY, .

MySQL, .

(T):

ID  | Column1 | Column2  |
----|---------+----------|
1   |    A    |    X     |
2   |    A    |    Y     |

MySQL

SELECT  ID, Column1, Column2
FROM    T
GROUP BY Column1;

SQL, MySQL, , , :

ID  | Column1 | Column2  |
----|---------+----------|
1   |    A    |    X     |

,

ID  | Column1 | Column2  |  
----|---------+----------|
2   |    A    |    Y     |

, , Column1, , , ? , , , , ORDER BY , , :

SELECT  ID, Column1, Column2
FROM    T
GROUP BY Column1
ORDER BY ID DESC;

, :

ID  | Column1 | Column2  |  
----|---------+----------|
2   |    A    |    Y     |

- ORDER BY ID DESC, ( ).

MySQL:

, , , . , ORDER BY.

, , , , .

SQL-Standard , GROUP BY , GROUP BY. SQL-2003-Standard:

15) T - , G - T. in, , T, C, G - QS.

, PRIMARY KEY, , , SQL MySQL ( Postgresql - , , - ):

SELECT  ID, Column1, Column2
FROM    T
GROUP BY ID;

, Column1, Column2 , .

Answer 3

group by - SQL. , SQL- .

, Oracle MySQL.

-, Oracle NULL . , Oracle:

select c, count(*)
from (select '' as c from dual union all
      select NULL from dual
     ) t
group by c;

"2". ( ?) ANSI 1.

, MySQL , select. , MySQL :

select a, b
from t
group by a;

. a t, ANSI. , , , , . Oracle, , , .

Another difference is the ordering of the results in group by. MySQL is deprecated by this feature, so no code should really depend on it. However, the result set is inherently disordered if there is no special offer order by, so two sets of results in different orders will be equivalent.