Why the given syntax is valid in mysql?

Question

In another answer I've spotted a weird syntax:

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

which was executed by mysql well though.

Why I think it should fail:

1. The subquery doesn't have alias
2. The whole query lacks SELECT clause

I find it unexpected to run and don't have an explanation why it works.

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

So, why is it valid? Any references?

Answer 1

It's the alternative UNION syntax with a final ORDER BY.

This is what such a union between two selects looks like:

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

And this is what such a union between one select looks like:

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

Not related to subqueries at all.

This isn't 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 when you want to sort the end result of the UNION.

The following would sort only the last SELECT:

SELECT …
UNION
SELECT …
UNION
SELECT … ORDER BY views

But this would sort the whole result:

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

You're doing something like this query, but you've got just one SELECT.