see see - 7 months ago 8
SQL Question

What is the name of the table resulting from a natural join?

I realized that MySQL doesn't ask me for giving an alias to the result of the natural join operation in this query:

SELECT * FROM TABLE1 NATURAL JOIN TABLE2
,
but isn't said table a derived one, and hence needs an alias?
This really is a followup-question to this one: Every derived table must have its own alias - when is something a derived table?, and I was told there that 'new' tables in the FROM-part of a query are derived tables and as such they need an alias.
If it indeed doesn't need an alias, how would I reference this new table in a subquery like this:
SELECT * FROM TABLE1 NATURAL JOIN TABLE2 WHERE NOT EXISTS (SELECT * FROM TABLE1 WHERE attribute1='thenewtable'.attribute2)
?

Answer

First, don't use NATURAL JOIN. It is a bug waiting to happen.

Why? Because it simply combines tables using the same names of columns. For instance, I often have a CreatedAt column in all my tables, and I never want to use that for joins. It is much, much, much preferred to use the USING clause instead, because this lists the columns explicitly being used.

Note that if the NATURAL JOIN used explicitly defined foreign key relationships, I would feel better about it. But, it ignores explicitly defined foreign key relationships.

In any case, the answer to your question is that you can still refer to columns in the tables using the table aliases defined in the query. So, you can write:

select table1.col1, table2.col2
from table1 natural join
     table2;

In this case the tables names are their own aliases (I would usually use explicit aliases that are table abbreviations such as t1 and t2).

The quote about derived tables is about subqueries in the FROM clause, which require an alias. There is no subquery in the FROM clause in your query.

The advantage of the SELECT * with a NATURAL JOIN is that it removes the duplicate column names. I don't think this is a good reason to use that syntax, however.

Comments