在具有复合 PK 的表上使用两次 where in 是否足够？

Question

我有一个查询

SELECT * 
FROM table1
WHERE
documentId in
(
   --select items from a second table where a third column happens to be null
   select documentId from table2 t2 inner join table1 t1
   on t1.documentId = t2.documentId and t1.itemId = t2.ItemId
   WHERE t1.someOtherColumn is null
)
and itemId in
(
   --similar query as above, just selecting itemId now
   select itemId from table2 t2 inner join table1 t1
   on t1.documentId = t2.documentId and t1.itemId = t2.ItemId
   WHERE t1.someOtherColumn is null
)
order by 1

鉴于 table1 具有复合 PK = documentId + itemId，这是否足以仅从 table1 中选择唯一的值？我担心的是，可能会出现 documentId 存在且 itemId 存在的情况，但由于它们没有一起查看，因此可能会做出错误的选择。

例如，

假设存在一个值

documentId = 1 且 itemId = 1。

假设没有组合键

documentId = 1 且 itemId = 1。

我不需要复合键

[documentId = 1 且 itemId = 3]

要包含的

。我也不想要复合键

[documentId = 2 且 itemId = 1]

要包含的

。如果后来添加了复合键（现在不存在）

[documentId = 1 且 itemId = 1]

那么它应该被包含在内。

Answer 1

这将执行相同的操作，而无需使用连接或两个 IN

SELECT *
FROM table1
WHERE exists
(
    SELECT 1
    FROM table2
    WHERE someOtherColumn is null
        AND table1.documentId = table2.documentId
        AND table1.itemId = table2.ItemId
)
ORDER BY 1

Answer 2

您的假设是正确的，这可能会导致错误的结果。你可以使用这个：

SELECT table1.* 
FROM table1
WHERE someOtherColumn IS NULL
  AND (documentId, itemId ) IN
   (
   SELECT documentId, itemId 
   FROM table2 t2                  --- no need for table1 again here 
   )
ORDER BY 1

---

更新：我认为上面的内容在 SQL-Server 中不起作用，仅在 Postgres（和 Oracle？）中。无论如何，这应该适用于大多数系统：

SELECT table1.* 
FROM table1
WHERE someOtherColumn IS NULL
  AND EXISTS
   (
   SELECT documentId, itemId FROM table2 t2     
   WHERE table2.documentId = table1.documentId
     AND table2.itemId = table1.itemId
   )
ORDER BY 1

Answer 3

这个怎么样：

SELECT * 
FROM table1,
(
   select documentId, itemid from table2 t2 inner join table1 t1
   on t1.documentId = t2.documentId and t1.itemId = t2.ItemId
   WHERE someOtherColumn is null
) t
WHERE
table1.documentId = t.documentid
and table1.itemId = t.itemid
order by 1