Why is this syntax valid in mysql?

Question

In another answer, I noticed a strange syntax:

(SELECT * FROM `articles` WHERE date >= UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 30 DAY)) ORDER BY `views` DESC LIMIT 20 ) ORDER by `views` ASC 

which mysql performed well, though.

Why I think this should fail:

• There is no alias in the subquery
• In general, the query does not have a SELECT

I find it unexpected to launch and have no explanation why it works.

It does not match the grammar defined at https://dev.mysql.com/doc/refman/5.5/en/select.html

So why is this really so? Any links?

Answer 1

This is an alternate UNION syntax with final ORDER BY .

This is what the union between the two choices looks like:

 (SELECT ...) UNION (SELECT ...) ORDER BY ... LIMIT ... 

And here is what the union between one choice is as follows:

 (SELECT ...) ORDER BY ... LIMIT ... 

Not applicable to subqueries at all.

This is not documented in MySQL, but is obvious from the grammar :

 top_level_select_init: SELECT_SYM { Lex->sql_command= SQLCOM_SELECT; } select_init2 | '(' select_paren ')' union_opt ; /* Need select_init2 for subselects. */ union_select_init: SELECT_SYM select_init2 | '(' select_paren ')' union_opt ; ... union_opt: /* Empty */ { $$= 0; } | union_list { $$= 1; } | union_order_or_limit { $$= 1; } ; 

Answer 2

The syntax is useful if you want to sort the final result of UNION.

The following will sort only the last SELECT:

 SELECT … UNION SELECT … UNION SELECT … ORDER BY views 

But this will sort the whole result:

 (SELECT …) UNION (SELECT …) UNION (SELECT …) ORDER BY views 

You are doing something like this query, but you have only one SELECT.