mysql - mysql中的USING()函数有什么用?
问题描述
我有 2 个要加入的表,可以通过 2 种方式实现:
客户:
+------------+--------------------+--------------------+--------------+
| 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 |
+------------+--------------------+--------------------+--------------+
订单:
+---------+------------+--------+------------+
| 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 |
+---------+------------+--------+------------+
现在我想只选择那些没有下订单的客户(OrderID == null
)。那只是Geogrge Napolitano
. 所以我可以使用两种方式:
mysql> select Customers.CustomerID, Customers.Name
-> from Customers left join Orders
-> on Customers.CustomerID = Orders.CustomerID
-> where Orders.OrderID is null;
或者
mysql> select Customers.CustomerID, Customers.Name
-> from Customers left join Orders
-> using(CustomerID)
-> where Orders.OrderID is null;
现在对我来说。似乎=
在第一种情况下 ( Customers.CustomerID = Orders.CustomerID
),与该 ID完全相同。USING()
但总是这样吗?在其他什么情况下我可以使用 mysql 功能USING()
?
解决方案
为了使用 Using,连接两个表的列名需要相同。
所以,
on Customers.CustomerID = Orders.CustomerID
等于
using(CustomerID)
当两边的列名相同时。