why I have to add parentheses to make union work

Question

tables:

create table movies (
movie_id int primary key,
title varchar(50));

insert into movies values
(1, 'avengers'), (2, 'frozen2'), (3, 'joker');

create table users (
user_id int primary key,
name varchar(50));

insert into users values
(1, 'daniel'), (2, 'monica'), (3, 'maria'), (4, 'james');

create table movie_rating (
movie_id int,
user_id int,
rating int,
created_at date,
primary key (movie_id, user_id));

insert into movie_rating values
(1, 1, 3, '2020-01-12'),
(1, 2, 4, '2020-02-11'),
(1, 3, 2, '2020-02-12'),
(1, 4, 1, '2020-01-01'),
(2, 1, 5, '2020-02-17'),
(2, 2, 2, '2020-02-01'),
(2, 3, 2, '2020-03-01'),
(3, 1, 3, '2020-02-22'),
(3, 2, 3, '2020-02-25');

question: Write the following SQL query:

Find the name of the user who has rated the greatest number of the movies. In case of a tie, return lexicographically smaller user name.

Find the movie name with the highest average rating in February 2020. In case of a tie, return lexicographically smaller movie name.

Query is returned in 2 rows, the query result format for above tables is:

Result table:
+--------------+
| results      |
+--------------+
| daniel       |
| frozen 2     |
+--------------+

My solution as below works only if I add parentheses both before and after union.

(select u.name results
from users u join movie_rating m on u.user_id = m.user_id
group by m.user_id, u.name
order by count(rating) desc, name
limit 1)
union
(select m.title results
from movies m join movie_rating r 
on m.movie_id = r.movie_id
and r.created_at between '2020-02-01' and '2020-02-29'
group by r.movie_id, m.title
order by avg(r.rating) desc, m.title
limit 1);

if I don't have parentheses I get error. Why do I have to use the parentheses to make union work?

select u.name results
from users u join movie_rating m on u.user_id = m.user_id
group by m.user_id, u.name
order by count(rating) desc, name
limit 1
union
select m.title results
from movies m join movie_rating r 
on m.movie_id = r.movie_id
and r.created_at between '2020-02-01' and '2020-02-29'
group by r.movie_id, m.title
order by avg(r.rating) desc, m.title
limit 1;

ERROR:  syntax error at or near "union"
LINE 6: union
        ^

user_6805190 · Answer

Without extra parentheses, LIMIT is only allowed once at the end of the query and applies to the result of the UNION. To attach a LIMIT to each SELECT parentheses are required.

Please check :: https://www.postgresql.org/docs/current/sql-select.html#SQL-UNION

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

user_7487039 · Answer

At https://dev.mysql.com/doc/refman/8.0/en/union.html

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT.

The reason is that the last clause [group by r.movie_id, m.title order by avg(r.rating) desc, m.title limit 1;] can apply for the last SELECT or for the UNION. Parenthesis resolve this syntactic ambiguity.

why I have to add parentheses to make union work

2 Answers