sql - 有条件的完全外连接的替代方案
问题描述
我需要比较两个表中的记录:X 和 Y。每条记录都有两个 ID:ID1 和 ID2。ID1 或 ID2 在任一表中都可以为空,但不能同时为空。我需要生成一个包含两个表中所有信息的视图:
- X.ID1 = Y.ID1 和 X.ID2 = Y.ID2 的行
- X.ID1 = Y.ID1 但 X.ID2 <> Y.ID2 的行
- X.ID1 <> Y.ID1 但 X.ID2 = Y.ID2 的行
- X.ID1 和 Y.ID1 根本没有任何匹配项的行
- X.ID2 和 Y.ID2 根本没有任何匹配项的行
例子:
X: Y:
|---------------| |---------------|
| ID1 | ID2 | | ID1 | ID2 |
|---------------| |---------------|
| 1 | A | | 1 | A |
| 2 | B | | 2 | C |
| 3 | NULL | | NULL | B |
| NULL | D | | 5 | NULL |
|---------------| |---------------|
Output:
|---------------------------------------|
| XID1 | YID1 | XID2 | YID2 | SRC |
|---------------------------------------|
| 1 | 1 | A | A | X+Y |
| 2 | 2 | B | C | X+Y |
| 3 | NULL | NULL | NULL | X |
| NULL | 5 | NULL | NULL | Y |
| 2 | NULL | B | B | X+Y |
| NULL | 2 | NULL | C | Y |
| NULL | NULL | D | NULL | X |
|---------------------------------------|
我的第一个明显的解决方案是做一个FULL OUTER JOIN
:
SELECT … FROM X FULL OUTER JOIN Y ON X.ID1 = Y.ID1 OR X.ID2 = Y.ID2
这行得通,但是连接中的条件性能很差,并且该视图最多需要一分钟才能运行。删除条件将执行时间缩短到不到一秒,但随后我失去了其中一个 ID 的匹配。
在不使用条件连接的情况下如何优雅地实现上述目标?我试过了:
- 通过连接两个 ID 进行连接,但这仅在两个 ID 匹配时才匹配
- 执行 a
CROSS JOIN
并按 过滤X.ID1=Y.ID1 OR X.ID2=Y.ID2
,但这会丢失没有任何匹配项的情况。这是最有前途的方法。 - 执行
UNION ALL
X 和 Y 的 a ,然后按 ID1 和 ID2 分组,但这再次仅在两个 ID 匹配时才匹配
解决方案
如果我正确阅读了您的条件,您可以尝试这样的事情。将左边的两个连接在一起并取两个集合中的一个不同的集合。
SELECT DISTINCT ... FROM (
SELECT … FROM X LEFT JOIN Y ON X.ID1 = Y.ID1
UNION ALL
SELECT … FROM X LEFT JOIN Y ON X.ID2 = Y.ID2
UNION ALL
SELECT … FROM Y LEFT JOIN X ON Y.ID1 = X.ID1 WHERE X.ID1 is null
UNION ALL
SELECT … FROM Y LEFT JOIN X ON Y.ID2 = X.ID2 WHERE X.ID2 is null
)
在我必须在连接中进行 OR 或两个左连接的联合之间进行选择的情况下,我发现联合更快。
编辑:更新为在左侧也包含 Y。
推荐阅读
- r - Replacing spread() with pivot_wider()
- codenameone - Codename One WebSocket + Spring Boot
- windows - Sanitize batch inputs against quotation marks?
- javascript - 如何添加方法
- android - Localytics Firebase 推送通知不适用于适用于 Android 的 Xamarin Forms
- go - 如何从 Cloud Identity Aware 代理获取用户电子邮件
- javascript - FilePond 插件导入错误 - 找不到“...”的声明文件
- python-3.x - 在 pyspark 数据帧上使用 POS 标记的 nltk wordnet 词形还原
- laravel - 无法在 laravel 迁移中创建关系
- python - How to Tokenize a list of lists of lists of strings