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 syntax elements. In this case, let’s consider the freely available SQL 1992 standard text (for simplicity), and see how it specifies table references:
<table reference> ::= <table name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ] | <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] | <joined table> <derived table> ::= <table subquery> <table subquery> ::= <subquery> <subquery> ::= <left paren> <query expression> <right paren>
The essence of the above syntax specification is this:
- A derived table MUST always be aliased
ASkeyword is optional, for improved readability
- The parentheses MUST always be put around subqueries
Following these rules, you’ll be pretty safe in most SQL dialects. Here are some deviations to the above, though:
- Some dialects allow for unaliased derived tables. However, this is still a bad idea, because you will not be able to unambiguously qualify a column from such a derived table
Always provide a meaningful alias to your derived tables. As simple as that.