SQL joining multiple tables on single column

Question

Can I use the USING clause to join more than 2 tables? i.e. can I do this

SELECT * FROM (a, b, c) USING(date)

or something similar instead of

SELECT * FROM a, b USING(date), c USING(date)

?

I am using MySQL.

EDIT
I see from the answers that I am misunderstood. The second example works fine, but just gets very long and verbose for many tables. I am looking for a cleaner syntax, not even more complicated ways to achieve this.

Answer 1

There is no such syntax to achieve what you want, however there is one thing that comes close:

It's kind of looked down upon, but you could look into using is the NATURAL JOIN syntax, where the condition(s) used for the join are implicit in that you don't need to specify the columns to join on. The implicit condition happens on columns that have the same name across the tables you're joining.

If date is the only column that has the same name across all of your tables, then you could do something like this:

SELECT *
FROM a
NATURAL JOIN b
NATURAL JOIN c

This will implicitly join the tables on the column date because the field name is the same in the tables.

The downside of this approach is of course the fact that you can't have any other columns with the same name in another table or else your joins will break as those columns will also be factored into the join condition unintentionally.

More on NATURAL JOIN

Answer 2

No, unfortunately you can't use the syntax in your example. It's not valid SQL.

Instead you could use any of the following:

You can use the explicit JOIN syntax to achieve what you want.

SELECT *
    FROM x
    LEFT JOIN y USING(date)
    LEFT JOIN z USING(date);

Usually for better control you'd use the ON clause.

SELECT *
    FROM x
    LEFT JOIN y ON x.date = y.date
    LEFT JOIN z ON y.date = z.date;