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 ?
user658459 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
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 allowsASand 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 bothT1andT2can be joined tables. Parentheses can be used aroundJOINclauses to control the join order.
1