sql - Postgres 复杂的全外连接保留 "on"列中的空值

Question

我编写了一个 PostgresSQL 查询，该查询在规模上性能相对较好，并为我提供了我想要的数据集，但我想知道这是否是编写查询的最简单/最佳方式。看起来应该有一个更简单的连接操作来满足我需要的条件。

编辑:我确实需要它在大型表上表现出色。在下面给出的示例中，宠物有 1.5 亿行，食物大约有 10 万行。我在底部的解决方案时钟大约为 0.6 毫秒。两个表都有一个关于 id 和 user_id 的索引。食物表还包括 pet_id 的索引。

我的系统中有两个相关的表，它们具有一个保证的共享属性 - user_id。这是一个本质上显示我的问题的示例:

宠物

+------+-------+---------+
|  id  | type  | user_id |
+------+-------+---------+
| 1234 | dog   |       1 |
| 1235 | cat   |       1 |
| 1236 | gecko |       1 |
+------+-------+---------+

食物

+------+-----------+---------+--------+
|  id  |   name    | user_id | pet_id |
+------+-----------+---------+--------+
| 4321 | hamburger |       1 | NULL   |
| 4322 | dog food  |       1 | 1234   |
| 4323 | cat food  |       1 | 1235   |
+------+-----------+---------+--------+

期望的结果

+------+------+
| p.id | f.id |
+------+------+
| NULL | 4321 |  --no pet, hamburger
| 1234 | 4322 |  --dog, dog food
| 1235 | 4323 |  --cat, cat food
| 1236 | NULL |  --gecko, no food
+------+------+

现在有一个例子可以引用，我会确保清楚结果是什么。结果包含来自两侧的所有属于我的 user_id 的行(想象一下该表可能包含数千个不属于 user_id 1 的其他行)。我希望这些结果行包含与另一个表匹配的每行的一个副本。

我尝试实现的完整外部连接示例:

SELECT p.id, f.id
FROM pets p FULL OUTER JOIN food f ON p.user_id = f.user_id
WHERE p.user_id = 1;

这个查询有点问题，因为

1. 它从查询的左侧排除 NULL。我需要那些。
2. 因为 user_id 在这里基本上是常量，所以我最终得到大量重复项，因为它与 user_id 匹配。左边的每一行都与右边的每一行相匹配。不是我需要的。我需要一对一的比赛。

我可以通过在 WHERE 过滤器中包含一个 OR 来修复 #1:

SELECT p.id, f.id
FROM pets p FULL OUTER JOIN food f ON p.user_id = f.user_id
WHERE p.user_id = 1 OR f.user_id = 1;

由于我不完全确定的原因，它使查询花费了很长时间。在我们的系统中，两个表都有一个user_id的索引，所以不是缺少索引。

为了解决我的问题，我提出了以下查询(实际上是两个组合):

SELECT p.id, f.id
    FROM pets p LEFT JOIN food f
        ON p.id = f.pet_id AND f.user_id = 1
    WHERE p.user_id = 1
UNION
SELECT p.id, f.id FROM pets p RIGHT JOIN food f
        ON p.id = f.pet_id
    WHERE f.user_id = 1 AND p.id IS NULL;

所以我的问题是:是否有更简单的方法将其作为单个查询执行？

Answer 1

SQL DEMO

SELECT p.id, f.id
FROM pets p 
FULL OUTER JOIN food f 
  ON p.user_id = f.user_id
 AND p.id = f.pet_id
 AND p.user_id = 1;

输出

|     id |     id |
|--------|--------|
|   1234 |   4322 |
|   1235 |   4323 |
|   1236 | (null) |
| (null) |   4321 |

注意:

您应该为两个表在 (user_id, pet_id) 上添加复合索引。