mysql - 从两个表中选择行并排除两个表中都存在的主键

Question

我有两个服务提供商表，providers 和 providers_clean。 providers 包含成千上万个数据格式非常糟糕的提供者，providers_clean 只有少数提供者仍然存在于“脏”表中。

我希望使用此数据的系统在用户“清理”数据时保持正常运行，因此我希望能够选择所有已“清理”的行和正在“清理”的行仍然是“脏”的，同时排除了与“干净”结果具有相同 ID 的任何“脏”结果。

我如何从 providers_clean 表中选择所有提供者并与 providers 表中的所有提供者合并，并排除那些已经被“清理”的提供者'

我试过:

SELECT * FROM providers WHERE NOT EXISTS (SELECT * FROM providers_clean WHERE providers_clean.id = providers.id)

这给了我所有来自 providers 的“脏”结果，不包括“干净”的结果，但是我如何重写查询以现在合并来自 的所有“干净”结果providers_clean?

这是我正在尝试做的事情的直观表示:

Clean Table

+----+-------------------+
| ID |       Name        |
+----+-------------------+
|  1 | Clean Provider 1  |
|  4 | Clean Provider 4  |
|  5 | Clean Provider 5  |
+----+-------------------+


Dirty Table
+----+------------------+
| ID |       Name       |
+----+------------------+
|  1 | Dirty Provider 1 |
|  2 | Dirty Provider 2 |
|  3 | Dirty Provider 3 |
|  4 | Dirty Provider 4 |
|  5 | Dirty Provider 5 |
+----+------------------+


Desired Result

+----+------------------+
| ID |       Name       |
+----+------------------+
|  1 | Clean Provider 1 |
|  2 | Dirty Provider 2 |
|  3 | Dirty Provider 3 |
|  4 | Clean Provider 4 |
|  5 | Clean Provider 5 |
+----+------------------+

谢谢

更新

这是可行的，但是，是否有更有效的方法来编写此查询？

SELECT providers.id AS id, 
CASE 
  WHEN 
   providers_clean.id IS NOT NULL 
  THEN 
   providers_clean.provider_name
  ELSE 
   providers.provider_name
END AS pname,

CASE 
  WHEN 
   providers_clean.id IS NOT NULL 
  THEN 
   providers_clean.phone
  ELSE 
   providers.phone
END AS pphone,

CASE 
  WHEN 
   providers_clean.id IS NOT NULL 
  THEN 
   providers_clean.website
  ELSE 
   providers.website
END AS pwebsite

FROM providers
  LEFT JOIN providers_clean ON providers_clean.id = providers.id
ORDER BY providers.id asc

Answer 1

你需要做一个从 Dirty 到 Clean 的外部连接(因为 Dirty 拥有 Clean 拥有的所有行，但反之则不然)

SELECT dirty.id AS id, 
CASE 
  WHEN clean.id IS NOT NULL THEN clean.name
  ELSE dirty.name
END AS new_name
FROM dirty
  LEFT JOIN clean ON clean.id = dirty.id
ORDER BY dirty.id asc

Example