Infrequent SQL developers often get confused about when to put
parentheses and/or aliases on derived tables. There has been
this recent Reddit
discussion about the subject, where user Elmhurstlol was wondering why they needed to
provide an alias to the derived table (the subselect with the
UNION
) in the following query:
SELECT AVG(price) AS AVG_PRICE FROM ( SELECT price from product a JOIN pc b ON a.model=b.model AND maker='A' UNION ALL SELECT price from product a JOIN laptop c ON a.model=c.model and maker='A' ) hello
The question really was about why the "hello"
table
alias was necessary, because often it seems not to be required.
Here’s what the SQL standard states
If in doubt, it is often useful to consider the SQL standard about the rationale behind some …
[Read more]