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
SELECT * FROM TABLE1 NATURAL JOIN TABLE2 WHERE NOT EXISTS (SELECT * FROM TABLE1 WHERE attribute1='thenewtable'.attribute2)
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
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.