-4

I have 2 tables which I want to join, and I can achieve it by 2 ways:

Customers:

+------------+--------------------+--------------------+--------------+
| CustomerID | Name               | Address            | City         |
+------------+--------------------+--------------------+--------------+
|          1 | Julie Smith        | 25 Oak Street      | Airport West |
|          2 | Alan Wong          | 1/47 Haines Avenue | Box Hill     |
|          3 | Michelle Arthur    | 357 North Road     | Yarraville   |
|          4 | Geogrge Napolitano | 177 Melbourne Road | Coburg       |
+------------+--------------------+--------------------+--------------+

Orders:

+---------+------------+--------+------------+
| OrderID | CustomerID | Amount | Date       |
+---------+------------+--------+------------+
|       1 |          3 |  69.98 | 2019-04-02 |
|       2 |          1 |  49.99 | 2019-04-15 |
|       3 |          2 |  74.99 | 2019-05-19 |
|       4 |          3 |  24.99 | 2019-05-01 |
+---------+------------+--------+------------+

Now I would like to select only those customers, that has no order placed (OrderID == null). That is only the Geogrge Napolitano. So I can use 2 ways:

mysql> select Customers.CustomerID, Customers.Name
    -> from Customers left join Orders 
    -> on Customers.CustomerID = Orders.CustomerID
    -> where Orders.OrderID is null;

or

mysql> select Customers.CustomerID, Customers.Name
    -> from Customers left join Orders
    -> using(CustomerID)
    -> where Orders.OrderID is null;

Now for me. It seems that = in first case (Customers.CustomerID = Orders.CustomerID), is exactly the same as USING() that Id. But is it always that case? In what other cases I can use the mysql function USING() ?

Martin
  • 22,212
  • 11
  • 70
  • 132
milanHrabos
  • 2,010
  • 3
  • 11
  • 45
  • *In what other cases I can use the mysql function USING() ?* USING is **not** a function. It is a clause. And it does not belong to MySQL only. *It seems that = in first case (Customers.CustomerID = Orders.CustomerID), is exactly the same as USING() that Id.* Exactly - no. Replace `SELECT {columns} FROM ...` in your queries with `SELECT * FROM ...` - and you will find one little difference. – Akina Sep 07 '20 at 19:56
  • You may (or may not) use the USING clause if and only if the joining field has exactly the same name on both sides of the join, and since it's a common practice to name that way fields related to id fields my guess is that has been included to simplify the coding. – Erick Sep 07 '20 at 23:57

1 Answers1

-1

In order to use Using, column names which connects two tables are needed to be same.

so,

 on Customers.CustomerID = Orders.CustomerID

equals to

using(CustomerID)

when column names are same on both sides.

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72