MySQL ON vs USING?

Generally, we use ON in MySQL. In a join, we use ON for a set of columns. USING is very useful when two tables share a column with the same name as the one they are connected to.

example.

Create our first table.

mysql>　CREATE　table　ForeignTableDemo
　　　->　(
　　　->　Id　int,
　　　->　Name　varchar(100),
　　　-　>　FK　int
　　　-　>　);

Create the second table.

mysql>　CREATE　table　PrimaryTableDemo
　　　-　>　(
　　　-　>　　FK　int,
　　　-　>　Address　varchar(100),
　　　-　>　primary　key(FK)
　　　-　>　);

Now let's add the constraint.

mysql>　ALTER　table　ForeignTableDemo　add　constraint　FKConst　foreign　key(FK)　references　PrimaryTableDemo(FK);
Records: 0  Duplicates: 0  Warnings: 0

The record is added to the second table.

mysql>　INSERT　into　PrimaryTableDemo　values(1,'US');
mysql>　INSERT　into　PrimaryTableDemo　values(2,'UK');
mysql> INSERT into PrimaryTableDemo values (3,'Unknown');

Display all records.

mysql> SELECT　*　from PrimaryTableDemo;

Here is the output.

+----+---------+
| FK      | Address      |
+----+---------+
|　　1　| US                  |
|　　2　| UK                  |
|　　3　| Unknown     |
+----+---------+
3　rows in set (0.00 sec)

Now, let's add records to the first table.

mysql> INSERT into ForeignTableDemo values (1,'John',1);
mysql> INSERT into ForeignTableDemo values (2,'Bob',2);

Now let's display all records from the first table.

mysql> SELECT　*　from ForeignTableDemo;

This is the output.

+------+------+------+
| Id      | Name      | FK      |
+------+------+------+
|　　　　1　| John     |　　　　1　|
|　　　　2　| Bob     |　　　　2　|
+------+------+------+
2　rows in set (0.00 sec)

Here is the direct join query that only displays matching rows. We have used it here.

mysql> SELECT ForeignTableDemo.Id, ForeignTableDemo.Name, PrimaryTableDemo.Address
　　　-　> from ForeignTableDemo
　　　-　> join PrimaryTableDemo
　　　-　> on ForeignTableDemo.FK = PrimaryTableDemo.FK;

Here is the output.

+------+------+---------+
| Id      | Name      | Address      |
+------+------+---------+
|　　　　1　| John | US                  |
|　　　　2　| Bob     | UK                  |
+------+------+---------+
2　rows in set (0.14　sec)

Example Usage.

Here is the syntax of using in MySQL, where it shows FK = 1records.

mysql> select　*from ForeignTableDemo join PrimaryTableDemo using(FK) where FK=1;

This is the output.

+------+------+------+---------+
| FK      | Id      | Name      | Address      |
+------+------+------+---------+
|　　　　1　|　　　　1　| John | US                  |
+------+------+------+---------+
1　row in set (0.09　sec)