CODEWITHSUNDEEP
mysql
user892134
user892134

Reputation: 3224

Don't understand USING syntax in MySQL

If i remove USING (nid,sid), i get the column is ambigious error. I don't quite understand what USING does here? Is it joining the nid and sid returned from the subquery to the nid, sid in the outer select query?

 SELECT sid, nid, max_rating FROM outcomes
JOIN (SELECT nid, sid, MAX(rating) AS max_rating from outcomes group by nid, sid) AS newtable
USING (nid, sid)
WHERE rating = max_rating AND name LIKE '%test%'

Upvotes: 0

Views: 810

Answers (2)

Aristotelis Kostopoulos
Aristotelis Kostopoulos

Reputation: 800

The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables: 

a LEFT JOIN b USING (c1,c2,c3)

The above is taken from the documentation of MySQL. I hope that this is helpfull.

Upvotes: 2

Subodh
Subodh

Reputation: 2214

Yes, USING is being used to join the two tables using both nid and sid. But it has added advantages.

  • First, your result will not have duplicate columns for nid and sid, as the USING statement merges the columns together.
  • Second, if you would like to use nid or sid in your WHERE clause, you would not need to specify which table to use for that column, you could directly use something like sid > 10.

Upvotes: 3

Related Questions