MySQL ON vs USING?
In a MySQL
JOIN, what is the difference between
USING()? As far as I can tell,
USING() is just more convenient syntax, whereas
ON allows a little more flexibility when the column names are not identical. However, that difference is so minor, you'd think they'd just do away with
Is there more to this than meets the eye? If yes, which should I use in a given situation?
It is mostly syntactic sugar, but a couple differences are noteworthy:
ON is the more general of the two. One can join tables ON a column, a set of columns and even a condition. For example:
SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...
USING is useful when both tables share a column of the exact same name on which they join. In this case, one may say:
SELECT ... FROM film JOIN film_actor USING (film_id) WHERE ...
An additional nice treat is that one does not need to fully qualify the joining columns:
SELECT film.title, film_id -- film_id is not prefixed FROM film JOIN film_actor USING (film_id) WHERE ...
To illustrate, to do the above with ON, we would have to write:
SELECT film.title, film.film_id -- film.film_id is required here FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) WHERE ...
film.film_id qualification in the
SELECT clause. It would be invalid to just say
film_id since that would make for an ambiguity:
ERROR 1052 (23000): Column 'film_id' in field list is ambiguous
select *, the joining column appears in the result set twice with
ON while it appears only once with
mysql> create table t(i int);insert t select 1;create table t2 select*from t; Query OK, 0 rows affected (0.11 sec) Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.19 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select*from t join t2 on t.i=t2.i; +------+------+ | i | i | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) mysql> select*from t join t2 using(i); +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql>
MySQL ON vs USING?, In general, we use ON in MySQL. In Joins, we use ON in a set of columns. USING is useful when both the tables share a column of the exact same name on which they join. In general, we use ON in MySQL. In Joins, we use ON in a set of columns. USING is useful when both the tables share a column of the exact same name on which they join. Example of On. Creating our first table. mysql> CREATE table ForeignTableDemo -> ( -> Id int, -> Name varchar(100), - > FK int - > ); Query OK, 0 rows affected (0.47 sec)
Thought I would chip in here with when I have found
ON to be more useful than
USING. It is when
OUTER joins are introduced into queries.
ON benefits from allowing the results set of the table that a query is
OUTER joining onto to be restricted while maintaining the
OUTER join. Attempting to restrict the results set through specifying a
WHERE clause will, effectively, change the
OUTER join into an
Granted this may be a relative corner case. Worth putting out there though.....
CREATE TABLE country ( countryId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, country varchar(50) not null, UNIQUE KEY countryUIdx1 (country) ) ENGINE=InnoDB; insert into country(country) values ("France"); insert into country(country) values ("China"); insert into country(country) values ("USA"); insert into country(country) values ("Italy"); insert into country(country) values ("UK"); insert into country(country) values ("Monaco"); CREATE TABLE city ( cityId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, countryId int(10) unsigned not null, city varchar(50) not null, hasAirport boolean not null default true, UNIQUE KEY cityUIdx1 (countryId,city), CONSTRAINT city_country_fk1 FOREIGN KEY (countryId) REFERENCES country (countryId) ) ENGINE=InnoDB; insert into city (countryId,city,hasAirport) values (1,"Paris",true); insert into city (countryId,city,hasAirport) values (2,"Bejing",true); insert into city (countryId,city,hasAirport) values (3,"New York",true); insert into city (countryId,city,hasAirport) values (4,"Napoli",true); insert into city (countryId,city,hasAirport) values (5,"Manchester",true); insert into city (countryId,city,hasAirport) values (5,"Birmingham",false); insert into city (countryId,city,hasAirport) values (3,"Cincinatti",false); insert into city (countryId,city,hasAirport) values (6,"Monaco",false); -- Gah. Left outer join is now effectively an inner join -- because of the where predicate select * from country left join city using (countryId) where hasAirport ; -- Hooray! I can see Monaco again thanks to -- moving my predicate into the ON select * from country co left join city ci on (co.countryId=ci.countryId and ci.hasAirport) ;
MySQL joins: ON vs. USING vs. Theta-style, SQL syntax is such a mess! ANSI style: USING. Is the special case where we join tables on columns of the same name, we can make a shortcut ANSI style: USING. Is the special case where we join tables on columns of the same name, we can make a shortcut and use USING: SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120. This time the parenthesis are required (I'm not sure why the difference on that part).
Wikipedia has the following information about
The USING construct is more than mere syntactic sugar, however, since the result set differs from the result set of the version with the explicit predicate. Specifically, any columns mentioned in the USING list will appear only once, with an unqualified name, rather than once for each table in the join. In the case above, there will be a single DepartmentID column and no employee.DepartmentID or department.DepartmentID.
Tables that it was talking about:
The Postgres documentation also defines them pretty well:
The ON clause is the most general kind of join condition: it takes a Boolean value expression of the same kind as is used in a WHERE clause. A pair of rows from T1 and T2 match if the ON expression evaluates to true.
The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one. For example, joining T1 and T2 with USING (a, b) produces the join condition ON T1.a = T2.a AND T1.b = T2.b.
Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.
SQL Joins with On or Using, In a nutshell, you use ON for most things, but USING is a handy mysql> select owners.name as owner, pets.name as pet, pets.animal -> from Both MySQL vs MSSQL Server behave well with multiple programming languages. Both RDBMS can be integrated with Java, PHP, C++, Python, Ruby, Visual Basic, Delphi, Go and R. However, MySQL additionally supports certain programming languages like Perl and Haskel which make it more popular among a wide range of developer community.
For those experimenting with this in phpMyAdmin, just a word:
phpMyAdmin appears to have a few problems with
USING. For the record this is phpMyAdmin run on Linux Mint, version: "188.8.131.52deb2ubuntu2", Database server: "10.2.14-MariaDB-10.2.14+maria~xenial - mariadb.org binary distribution".
I have run
SELECT commands using
USING in both phpMyAdmin and in Terminal (command line), and the ones in phpMyAdmin produce some baffling responses:
LIMIT clause at the end appears to be ignored.
2) the supposed number of rows as reported at the top of the page with the results is sometimes wrong: for example 4 are returned, but at the top it says "Showing rows 0 - 24 (2503 total, Query took 0.0018 seconds.)"
Logging on to mysql normally and running the same queries does not produce these errors. Nor do these errors occur when running the same query in phpMyAdmin using
JOIN ... ON .... Presumably a phpMyAdmin bug.
MySQL NATURAL JOIN, is useful when both the tables share a column of the exact same name on which they join. Example of On. To add records into the second table. I usually work on VS Code. When I work with MySQL, I don’t want to use CLI or switch to another standalone tool to manage MySQL. Here we have a lightweight MySQL management tool for VS Code. You could easily manage MySQL inside your Visual Studio Code! Manage MySQL Connections.
MySQL 8.0 Reference Manual :: 184.108.40.206 JOIN Clause, to join are called owners_id in both tables, then we can instead put in USING as a shorthand. This USING trick is why you will often see fields named, for example, "user_id" when they are in the "users" table - then the shorthand can be used any time you join this user_id to any other user_id column. This entry was posted in tech and tagged join, mysql, sql by .
SQL, If there is no matching row for the right table in the ON or USING part in a LEFT JOIN , a row with all columns set to NULL is used for the right table. You can use MySQL provides you with a suite of tools for developing and managing MySQL-based business critical applications on Windows. It now only takes 3 minutes from downloading the MySQL Installer to having a ready to use MySQL system on your machine! An easy to use wizard based UI will guide you through the installation process.
SQL Joins Using WHERE or ON, PHP | MySQL WHERE Clause · SQL | Distinct Clause · SQL | SELECT TOP Clause · SQL | With Ties Clause · SQL | Sub queries in From Clause · SQL | Intersect & The easiest way to install MySQL is to use the MySQL repositories : For Yum-based Linux distributions like Oracle Linux, Red Hat Enterprise Linux, and Fedora, follow the instructions in A Quick Guide to Using the MySQL Yum Repository. If your system cannot use the MySQL Yum repository for some reason,