Aliasing conditions in Postgresql

So I am new to Postgresl and am trying to get familiar with nested queries and how they work as there seems to be some conditions that add to what intuition seems to tell me.

I learned, at least I thought I learned, that in Postgresql, aliases where required in FROM clauses that involved a subquery. In trying to implement division I found at the following :

The query below works, although I do not alias the derived table from which I select :

SELECT *
FROM (
    (SELECT state_id from state_drinks) AS state_ids
CROSS JOIN
    (SELECT id FROM drinks) AS drink_ids
) -- NO ALIAS FOR OUTTER SELECT BUT WORKS;

However, the more complex query below (in which the query above is nested), requires aliasing :

SELECT state_id
FROM (
    SELECT *
    FROM (
        (SELECT state_id from state_drinks) AS state_ids
    CROSS JOIN
        (SELECT id FROM drinks) AS drink_ids
    ) AS all_tuples
    EXCEPT
    SELECT *
    FROM state_drinks AS actual_tuples
) AS eliminated_tuples --DOES NOT WORK ----WITHOUT 'As eliminated_tuples'

Since both queries involve selecting from a dervied table, gotten from performing a subquery involving two other derived tables and an opeartion (CROSS JOIN in the first query and EXCEPT in the second), why is it a must to alias in the second query and not the first ?

Solution

The outer parenthesis on the first query does not make a derived table (what you are calling a subquery). It just specifies the join nesting order, which in this case doesn't matter because you only have two tables.

You can for example have

SELECT *
FROM state_drinks AS si
LEFT JOIN (
    drinks AS di
    CROSS JOIN food f
) ON someConditionHere;

which means the result of the cross-join is left-joined back to state_drinks, rather than the result of the left-join being cross-joined.

A derived table is specifically defined as

7.2.1.3. Subqueries

Subqueries specifying a derived table must be enclosed in parentheses and must be assigned a table alias name
FROM (SELECT * FROM table1) AS alias_name

This changed in Postgres 16 to

7.2.1.3. Subqueries

Subqueries specifying a derived table must be enclosed in parentheses. They may be assigned a table alias name...
FROM (SELECT * FROM table1) AS alias_name
...snip...
According to the SQL standard, a table alias name must be supplied for a subquery. PostgreSQL allows AS and the alias to be omitted, but writing one is good practice in SQL code that might be ported to another system.

But parenthesis around joins is defined separately:

7.2.1.1. Joined Tables

...snip...
Joins of all types can be chained together, or nested: either or both T1 and T2 can be joined tables. Parentheses can be used around JOIN clauses to control the join order.