2

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)?

Community
  • 1
  • 1
azureai
  • 181
  • 1
  • 13
  • 1
    No. It's the difference between `SELECT 1` and `SELECT * FROM (SELECT 1)`, say – Strawberry Apr 03 '16 at 13:40
  • 1
    A recordset from a select query is not a new table. It doesn't matter whether that query has a join or if it does, what kind. As far as what you appear to be attempting, you might be able to simply use a simple where clause `where attribute1 <> attribute2`. – Dan Bracuk Apr 03 '16 at 13:48
  • 1
    maybe interesting? It turns out 'natural join' to be 'less than good'. I suggest reading some of the upvoted comments. - [Difference between natural join and inner join](http://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join). – Ryan Vincent Apr 03 '16 at 14:05

1 Answers1

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786