when or why using mysql AS and USING?

Question

Can anyone explain to me why they use AS id and AS r2 and USING id on the codes :

SELECT id, username FROM friendship JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM friendship)) AS id) AS r2 USING (id);

I just want to know the purpose of using them, why using them?

Oscar Gomez · Accepted Answer · 2011-07-04 05:30:33Z

1

The As is just to create an Alias for the subquery, that way you can use that same alias 'id' with the using command.

Basically the query wants the friendship table to join the subquery on the id column, since the subquery only returns one result it can join with the id column in the friendship table.

edited Jul 4, 2011 at 5:30

answered Jul 4, 2011 at 5:25

Oscar Gomez

18.5k14 gold badges88 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

zac1987 Over a year ago

why need to create an alias? The subquery will returns the same one result too even without using As id, they the subquery still can join the id column in friendship table if without writing As id, so why they need to write As id? What is the different with As id and without As id?

Oscar Gomez Over a year ago

@Zac1987 It is true that both will have the id column, however because the join clause is not with an existing table but with a subquery result you need to specify the join condition. I am pretty sure it will not work otherwise.

zac1987 Over a year ago

so As id is to specify the JOIN condition? Usually we specify the JOIN condition like ON table1.id = table2.id; so AS id is just the same like table2.id ?

Oscar Gomez Over a year ago

@Zac1987 As Id is just to identify the result of the subquery, and yes we are using it to specify the join condition, using is a shortcut for join on table.id = mysubquery.id. Yes in this scenario it would be the same.

Vincent Savard · Accepted Answer · 2011-07-04 05:29:53Z

OscarMk already explained the AS, but the USING is the equivalent of ON table1.id = table2.id. By using USING (a bit redundant here...), your RDBMS basically merge the columns into a single one, which means you don't have to tell your RDBMS if you want the column from table1 or table2. Simple example :

SELECT table1.id FROM table1 INNER JOIN table2 ON table1.id = table2.id; SELECT id FROM table1 INNER JOIN table2 USING (id);

Those queries are equivalent. If you had forgotten to use table1.id in the first query, your RDBMS would have raised an error.

You can usually do a NATURAL JOIN instead of a INNER JOIN, and not use a USING or ON clause at all (that's what NATURAL JOIN are for)

in my case, both are the same table, why need to join the id in a same table since the id is the same?

Collectives™ on Stack Overflow

when or why using mysql AS and USING?

2 Answers 2

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Related