0

I'm trying to understand the logical order of execution of the SELECT query in the MySQL DBMS.

Reading this and this questions it came out that the order can be:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Or:

  1. FROM clause
  2. WHERE clause
  3. SELECT clause
  4. GROUP BY clause
  5. HAVING clause
  6. ORDER BY clause

As you can see the order is inverse in the two, so I started fiddling with MySQL.

The table (PROGETTO) was the following:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| NOME_P   | varchar(15) | NO   | UNI | NULL    |       |
| NUMERO_P | int(11)     | NO   | PRI | NULL    |       |
| SEDE_P   | varchar(15) | YES  |     | NULL    |       |
| NUM_D    | int(11)     | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

the dataset was:

+-----------------+----------+-----------+-------+
| NOME_P          | NUMERO_P | SEDE_P    | NUM_D |
+-----------------+----------+-----------+-------+
| ProdottoX       |        1 | Bellaire  |     5 |
| ProdottoY       |        2 | Sugarland |     5 |
| ProdottoZ       |        3 | Houston   |     5 |
| Informatizzazio |       10 | Stafford  |     4 |
| Riorganizzazion |       20 | Houston   |     1 |
| Nuove opportuni |       30 | Stafford  |     4 |
+-----------------+----------+-----------+-------+

So, I launched the first query:

mysql> SELECT NOME_P FROM PROGETTO GROUP BY NUM_D;
+-----------------+
| NOME_P          |
+-----------------+
| Riorganizzazion |
| Informatizzazio |
| ProdottoX       |
+-----------------+

and I thought, "well the order must be the first one, but let's try another time..." and I executed:

mysql> SELECT NOME_P AS NNN FROM PROGETTO GROUP BY  NNN;
+-----------------+
| NNN             |
+-----------------+
| Informatizzazio |
| Nuove opportuni |
| ProdottoX       |
| ProdottoY       |
| ProdottoZ       |
| Riorganizzazion |
+-----------------+

...and this totally made me confused.

Community
  • 1
  • 1
zer0uno
  • 7,521
  • 13
  • 57
  • 86
  • The difference in the results has nothing to do with *order of execution*. You are grouping by *two different fields*, hence the different output. – Giorgos Betsos Jun 25 '16 at 18:00
  • I'm talking about "NNN" not the result. Following the first one order WHERE can't access to the alias defined in the `SELECT` and so should be for `GROUP BY`, but it isn't – zer0uno Jun 25 '16 at 18:15
  • This is like an extension of the `GROUP BY` clause implemented by the MySQL engine. In another RDBMS like SQL Server you would get a syntax error for the same statement. – Giorgos Betsos Jun 25 '16 at 18:18

0 Answers0