sql-server - 只是对 SQL 连接感到好奇

Question

我只是好奇这里。如果我有两个表，假设是 Clients 和 Orders。客户端有一个唯一的主键 ID_Client。订单也有一个 ID_Client 字段和一个关系，以通过 ID_Client 字段维护与客户表的完整性。

所以当我想加入两个表时，我会这样做:

SELECT 
    Orders.*, Clients.Name 
FROM 
    Orders 
INNER JOIN 
    Clients ON Clients.ID_Client = Orders.ID_Client

因此，如果我负责创建主键以及表之间的关系，

为什么我需要在 on 子句中明确包含连接的列？

为什么我不能做类似的事情:

SELECT 
    Orders.*, Clients.Name 
FROM 
    Orders 
INNER JOIN 
    Clients 

所以 SQL 应该知道哪些列与两个表相关......

Answer 1

我曾经遇到过同样的问题，我在 Database Administrator Stack Exchange 上找到了很好的解释，下面的答案是我认为最好的答案，但您也可以引用链接以获取更多解释。

A foreign key is meant to constrain the data. ie enforce referential integrity. That's it. Nothing else.

1. You can have multiple foreign keys to the same table. Consider the following where a shipment has a starting point, and an ending point.

   table: USA_States
   StateID
   StateName
   
   table: Shipment
   ShipmentID
   PickupStateID Foreign key
   DeliveryStateID Foreign key
   

   You may want to join based on the pickup state. Maybe you want to join on the delivery state. Maybe you want to perform 2 joins for both! The sql engine has no way of knowing what you want.

2. You'll often cross join scalar values. Although scalars are usually the result of intermediate calculations, sometimes you'll have a special purpose table with exactly 1 record. If the engine tried to detect a foriegn key for the join.... it wouldn't make sense because cross joins never match up a column.

3. In some special cases you'll join on columns where neither is unique. Therefore the presence of a PK/FK on those columns is impossible.

4. You may think points 2 and 3 above are not relevant since your questions is about when there IS a single PK/FK relationship between tables. However the presence of single PK/FK between the tables does not mean you can't have other fields to join on in addition to the PK/FK. The sql engine would not know which fields you want to join on.

5. Lets say you have a table "USA_States", and 5 other tables with a FK to the states. The "five" tables also have a few foreign keys to each other. Should the sql engine automatically join the "five" tables with "USA_States"? Or should it join the "five" to each other? Both? You could set up the relationships so that the sql engine enters an infinite loop trying to join stuff together. In this situation it's impossible fore the sql engine to guess what you want.

In summary: PK/FK has nothing to do with table joins. They are separate unrelated things. It's just an accident of nature that you often join on the PK/FK columns.

Would you want the sql engine to guess if it's a full, left, right, or inner join? I don't think so. Although that would arguably be a lesser sin than guessing the columns to join on.