sql - 在具有复合 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 and itemId = 1.

假设没有复合键

documentId = 1 and itemId = 1.

我不要复合键

[documentId = 1 and itemId = 3]

被包括。

我也不想要复合键

[documentId = 2 and itemId = 1]

被包括。

如果后来添加了复合键(现在不存在)

[documentId = 1 and 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