Every derived table must have its own alias, but alias given to all sub-queries gives error

Question

The following MySQL query on a relational database generates the error "Every derived table must have its own alias". Based on every derived table must have its own alias and Every derived table must have its own alias error. I give aliases to every table in sub-queries and the resultant table of the unions. Yet that did not seem to fix it. Can someone explain what is still missing in here?

SELECT major
FROM
    (SELECT major, count(*) amount
    FROM
            (((SELECT major
            FROM
                    (SELECT tutorID
                    FROM questionAnsweredBy
                    AS t1)
                    NATURAL JOIN
                    (SELECT tutorID, major
                    FROM tutor
                    AS t2) AS t12
            )
            AS t3
            UNION
            (SELECT major
            FROM
                    (SELECT tutorID
                    FROM expertAnswerGivenBy
                    AS t4)
                    NATURAL JOIN
                    (SELECT tutorID, major
                    FROM tutor
                    AS t5) AS t13
            )
            AS t6) AS t15
            UNION
            (SELECT major
            FROM
                    (SELECT tutorID
                    FROM textbookSolutionGivenBy
                    AS t7)
                    NATURAL JOIN
                    (SELECT tutorID, major
                    FROM tutor
                    AS t8) AS t14
            )
            AS t9) AS t16
    GROUP BY major
    AS t10
    )
AS t11

I don't see aliases applied to the subqueries immediately before each `NATURAL JOIN`. — Damien_The_Unbeliever, Nov 24 '17 at 08:39
I believe t1, t4, and t7 are aliases for those queries right? — Rentian Dong, Nov 24 '17 at 08:48
No, those are aliases for the `questionAnsweredBy`, `expertAnswerGivenBy` and `textbookSolutionGivenBy` tables *within* the subqueries. They're not aliases for the subqueries themselves (subquery aliases appear *after* the closing bracket that encloses the subquery. — Damien_The_Unbeliever, Nov 24 '17 at 08:51
There is no point to your `group by`-`count` & outermost `select` since the latter just undoes the former. Is that a mistake or is this just a syntax experiment or work in progress etc? — philipxy, Nov 24 '17 at 11:03

philipxy · Accepted Answer · 2017-11-24T12:00:14.803

The NATURAL JOIN right hand argument subqueries lack aliases. The subqueries that are arguments to the UNIONs should not have aliases.

A FROM argument is table or table [AS] alias or (select ...) [AS] alias. Very common is to use AS in SELECT clauses for columns but not use it in FROM clauses for tables/subqueries. (Not all DBMSs support the latter.) You can chain UNIONs without parentheses, as withJOINs.

Let's align a subquery's parentheses vertically. Let's vertically align SELECT, FROM, JOIN, UNION & GROUP BY with arguments indented.

When you want to understand something technical, start with the official documentation.

SELECT major
FROM
    (SELECT major, count(*) AS amount
    FROM
        (
            (SELECT major
            FROM
                (SELECT tutorID
                FROM questionAnsweredBy t1
                ) nj1
            NATURAL JOIN
                (SELECT tutorID, major
                FROM tutor t2
                ) t12
            )
        UNION
            (SELECT major
            FROM
                (SELECT tutorID
                FROM expertAnswerGivenBy t4
                ) nj2
            NATURAL JOIN
                (SELECT tutorID, major
                FROM tutor t5
                ) AS t13
            )
        UNION
            (SELECT major
            FROM
                (SELECT tutorID
                FROM textbookSolutionGivenBy t7
                ) nj3
            NATURAL JOIN
                (SELECT tutorID, major
                FROM tutor t8
                ) t14
            )
        ) t16
    GROUP BY major
    ) t10

PS Re simplifying further:

There is no point to your group by-count(*) & outermost select since the latter just undoes the former. Anyway all the count(*)s are 1 because major is unique in the union result.

For from arguments that are base table names you don't need to select. And you don't need an alias. Especially if you never dot it. You also don't need a from subselect alias to differ from its ancestor froms' aliases. It's common to use some standard name/prefix suggesting an unused alias, eg x or dummy.

You seem to be mapping from relational algebra to SQL. Presumably your base tables have no duplicate rows, duplicate columns or nulls. You might as well select * from joins unless you want the select to represent a projection. Ideally use union corresponding but if it's not supported then explicitly list column names so they're paired correctly; either always or where columns can be in different order. You need to remove duplicates via select distinct; either always or where duplicates can arise--selecting on a non-superkey.

SELECT major
FROM
    (SELECT major, count(*) AS amount
    FROM
        (
            (SELECT major
            FROM questionAnsweredBy
            NATURAL JOIN tutor
            )
        UNION
            (SELECT major
            FROM expertAnswerGivenBy
            NATURAL JOIN tutor
            )
        UNION
            (SELECT major
            FROM textbookSolutionGivenBy
            NATURAL JOIN tutor
            )
        ) x
    GROUP BY major
    ) x

I just added a PS re simplifying further. – philipxy Nov 24 '17 at 11:53 — philipxy, Nov 24 '17 at 11:53

Every derived table must have its own alias, but alias given to all sub-queries gives error

1 Answers1