In MySQL v5.7 using the InnoDB engine, I have a table called birds and another one called conservation_status, which look like the following:
mysql> describe birds;
+------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+----------------+
| bird_id | int(11) | NO | PRI | NULL | auto_increment |
| scientific_name | varchar(100) | YES | UNI | NULL | |
| common_name | varchar(255) | YES | | NULL | |
| family_id | int(11) | YES | | NULL | |
| conservation_status_id | int(11) | YES | | NULL | |
| wing_id | char(2) | YES | | NULL | |
| body_id | char(2) | YES | | NULL | |
| bill_id | char(2) | YES | | NULL | |
| description | text | YES | | NULL | |
+------------------------+--------------+------+-----+---------+----------------+
mysql> describe conservation_status;
+------------------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+----------+------+-----+---------+----------------+
| conservation_status_id | int(11) | NO | PRI | NULL | auto_increment |
| conservation_category | char(10) | YES | | NULL | |
| conservation_state | char(25) | YES | | NULL | |
+------------------------+----------+------+-----+---------+----------------+
I'm experimenting with the USING clause, and I'm trying to join the two tables on the conservation_status_id column with such a clause, but I get an error related to my use of the column name:
select * from birds join conservation_status USING conservation_status_id;
I know the use case of USING is for when the column used to join two tables has the same name in each table, therefore the problem can't be an ambiguous column name match.
I've verified in the MySQL docs that my version of MySQL supports this clause, and I've verified that there are no typos in the column name.
What am I doing wrong?
The argument to USING must be in parentheses. It's not just a column identifier, it's a tuple.
select * from birds join conservation_status USING (conservation_status_id);
It needs to be a tuple because you can join on multiple columns, for example:
select * from table1 join table2 USING (col1, col2, col3)