SQL 在同一张桌子上加入两次的最佳方式是什么？

What's the best way to join on the same table twice?

提问by froadie

This is a little complicated, but I have 2 tables. Let's say the structure is something like this:

这有点复杂，但我有 2 张桌子。假设结构是这样的：

*Table1*
ID
PhoneNumber1
PhoneNumber2

*Table2*
PhoneNumber
SomeOtherField

The tables can be joined based on Table1.PhoneNumber1 -> Table2.PhoneNumber, or Table1.PhoneNumber2 -> Table2.PhoneNumber.

这些表可以基于 Table1.PhoneNumber1 -> Table2.PhoneNumber 或 Table1.PhoneNumber2 -> Table2.PhoneNumber 进行连接。

Now, I want to get a resultset that contains PhoneNumber1, SomeOtherField that corresponds to PhoneNumber1, PhoneNumber2, and SomeOtherField that corresponds to PhoneNumber2.

现在，我想获得一个结果集，其中包含 PhoneNumber1、对应于 PhoneNumber1、PhoneNumber2 的 SomeOtherField 和对应于 PhoneNumber2 的 SomeOtherField。

I thought of 2 ways to do this - either by joining on the table twice, or by joining once with an OR in the ON clause.

我想到了两种方法来做到这一点 - 要么在表上加入两次，要么在 ON 子句中用 OR 加入一次。

Method 1:

方法一：

SELECT t1.PhoneNumber1, t1.PhoneNumber2, 
   t2.SomeOtherFieldForPhone1, t3.someOtherFieldForPhone2
FROM Table1 t1
INNER JOIN Table2 t2
   ON t2.PhoneNumber = t1.PhoneNumber1
INNER JOIN Table2 t3
   ON t3.PhoneNumber = t1.PhoneNumber2

This seems to work.

这似乎有效。

Method 2:

方法二：

To somehow have a query that looks a bit like this -

以某种方式有一个看起来像这样的查询 -

SELECT ...
FROM Table1
INNER JOIN Table2 
   ON Table1.PhoneNumber1 = Table2.PhoneNumber OR
      Table1.PhoneNumber2 = Table2.PhoneNumber

I haven't gotten this to work yet and I'm not sure if there's a way to do it.

我还没有让它起作用，我不确定是否有办法做到这一点。

What's the best way to accomplish this? Neither way seems simple or intuitive... Is there a more straightforward way to do this? How is this requirement generally implemented?

实现这一目标的最佳方法是什么？两种方法都看起来不简单或直观......有没有更直接的方法来做到这一点？这个要求一般是如何实施的？

回答by Paul Sasik

First, I would try and refactor these tables to get away from using phone numbers as natural keys. I am not a fan of natural keys and this is a great example why. Natural keys, especially things like phone numbers, can change and frequently so. Updating your database when that change happens will be a HUGE, error-prone headache. *

首先，我会尝试重构这些表，以避免使用电话号码作为自然键。我不喜欢自然键，这是一个很好的例子。自然键，尤其是电话号码之类的东西，可以经常更改。在发生这种变化时更新您的数据库将是一个巨大的、容易出错的头痛问题。*

Method 1as you describe it is your best bet though. It looks a bit terse due to the naming scheme and the short aliases but... aliasing is your friend when it comes to joining the same table multiple times or using subqueries etc.

不过，您所描述的方法 1是您最好的选择。由于命名方案和短别名，它看起来有点简洁，但是......在多次加入同一个表或使用子查询等时，别名是你的朋友。

I would just clean things up a bit:

我只想清理一下：

SELECT t.PhoneNumber1, t.PhoneNumber2, 
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2
FROM Table1 t
JOIN Table2 t1 ON t1.PhoneNumber = t.PhoneNumber1
JOIN Table2 t2 ON t2.PhoneNumber = t.PhoneNumber2

What i did:

我做了什么：

• No need to specify INNER - it's implied by the fact that you don't specify LEFT or RIGHT
• Don't n-suffix your primary lookup table
• N-Suffix the table aliases that you will use multiple times to make it obvious

• 无需指定 INNER - 这暗示您没有指定 LEFT 或 RIGHT
• 不要给你的主查找表添加 n 后缀
• N-后缀您将多次使用的表别名以使其显而易见

*One way DBAs avoid the headaches of updating natural keys is to not specify primary keys and foreign key constraints which further compounds the issues with poor db design. I've actually seen this more often than not.

* DBA 避免更新自然键的麻烦的一种方法是不指定主键和外键约束，这进一步加剧了数据库设计不佳的问题。我实际上经常看到这种情况。

回答by HLGEM

The first is good unless either Phone1 or (more likely) phone2 can be null. In that case you want to use a Left join instead of an inner join.

第一个是好的，除非 Phone1 或（更有可能）phone2 可以为空。在这种情况下，您想使用左连接而不是内连接。

It is usually a bad sign when you have a table with two phone number fields. Usually this means your database design is flawed.

当您有一个包含两个电话号码字段的表格时，这通常是一个不好的迹象。通常这意味着您的数据库设计存在缺陷。

回答by Pointy

You could use UNIONto combine two joins:

您可以使用UNION组合两个连接：

SELECT Table1.PhoneNumber1 as PhoneNumber, Table2.SomeOtherField as OtherField
  FROM Table1
  JOIN Table2
    ON Table1.PhoneNumber1 = Table2.PhoneNumber
 UNION
SELECT Table1.PhoneNumber2 as PhoneNumber, Table2.SomeOtherField as OtherField
  FROM Table1
  JOIN Table2
    ON Table1.PhoneNumber2 = Table2.PhoneNumber

回答by F1iX

My problem was to display the record even if no or only one phone number exists(full address book). Therefore I used a LEFT JOIN which takes all records from the left, even if no corresponding exists on the right. For me this works in Microsoft AccessSQL (they require the parenthesis!)

我的问题是即使不存在或只有一个电话号码（完整地址簿）也显示记录。因此，我使用了一个 LEFT JOIN，它从左侧获取所有记录，即使右侧不存在对应的记录。对我来说，这适用于Microsoft AccessSQL（它们需要括号！）

SELECT t.PhoneNumber1, t.PhoneNumber2, t.PhoneNumber3
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2, t3.someOtherFieldForPhone3
FROM 
(
 (
  Table1 AS t LEFT JOIN Table2 AS t3 ON t.PhoneNumber3 = t3.PhoneNumber
 )
 LEFT JOIN Table2 AS t2 ON t.PhoneNumber2 = t2.PhoneNumber
)
LEFT JOIN Table2 AS t1 ON t.PhoneNumber1 = t1.PhoneNumber;

回答by Nelson Rothermel

The first method is the proper approach and will do what you need. However, with the inner joins, you will only select rows from Table1if both phone numbers exist in Table2. You may want to do a LEFT JOINso that all rows from Table1are selected. If the phone numbers don't match, then the SomeOtherFields would be null. If you want to make sure you have at least one matching phone number you could then do WHERE t2.PhoneNumber IS NOT NULL OR t3.PhoneNumber IS NOT NULL

第一种方法是正确的方法，可以满足您的需求。但是，对于内部联接，Table1如果两个电话号码都存在于Table2. 您可能想要执行 aLEFT JOIN以便选择所有行Table1。如果电话号码不匹配，则SomeOtherFields 将为空。如果你想确保你至少有一个匹配的电话号码，你可以这样做WHERE t2.PhoneNumber IS NOT NULL OR t3.PhoneNumber IS NOT NULL

The second method could have a problem: what happens if Table2has both PhoneNumber1and PhoneNumber2? Which row will be selected? Depending on your data, foreign keys, etc. this may or may not be a problem.

第二种方法可能有问题：如果Table2同时有PhoneNumber1和会发生什么PhoneNumber2？将选择哪一行？根据您的数据、外键等，这可能是也可能不是问题。