如何確定行是否從其他表中引用？

我有一個名為contacts(primary key id) 的表，它被許多其他表通過外鍵引用。當應用程序中顯示聯繫人列表時，我將他們的主鍵放在一個數組中，並需要確定這些記錄是否可以刪除。這僅用於顯示，此處沒有實際刪除。

根據這個答案，我可以確定是否可以刪除一條記錄：

(SELECT 1 FROM case_contacts WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM case_payments WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM invoices WHERE contact_id = 5000 LIMIT 1)
-- etc
LIMIT 1;

我可以對給定的每個聯繫人 ID 執行此查詢，但這似乎非常低效。

有沒有辦法為多個聯繫人 ID 發送單個查詢（一次最多約 500 個）？任何類型的結果都可以，只包括可以刪除的 ID，或者只包括不能刪除的 ID，或者(contact_id, can_be_deleted).

---

編輯：這是我正在使用的，直到我找到更有效的解決方案：

SELECT  c.id
 FROM  contacts c
WHERE  c.id IN (1,10,20,1557,5000,15057)
       AND NOT EXISTS (SELECT 1 FROM case_contacts WHERE contact_id = c.id)
       AND NOT EXISTS (SELECT 1 FROM case_payments WHERE contact_id = c.id)
       AND NOT EXISTS (SELECT 1 FROM invoices      WHERE contact_id = c.id);

您可能需要為 postgres 稍微改變一下（是 MS SQL 語法），但類似於：

SELECT id
FROM   contacts
LEFT OUTER JOIN FROM case_contacts ON case_contacts.contact_id = contacts.id
LEFT OUTER JOIN FROM case_payments ON case_payments.contact_id = contacts.id
LEFT OUTER JOIN FROM invoices      ON      invoices.contact_id = contacts.id
WHERE case_contacts.contact_id IS NULL
AND   case_payments.contact_id IS NULL
AND        invoices.contact_id IS NULL

要麼：

SELECT id
FROM   contacts
WHERE  id NOT IN (SELECT contact_id FROM case_contacts)
AND    id NOT IN (SELECT contact_id FROM case_payments)
AND    id NOT IN (SELECT contact_id FROM invoices)

應該列出這三個表中未提及的 ID。查詢規劃器 * 應該 * 將這些視為等效並進行相應優化，但如果不是，則前者將更有效（後者有可能對 中的每一行執行三個子查詢一次contacts​​）。

對於簡單的結果（只是id），**EXCEPT**可能是最簡單和最快的：

另外，既然你寫：

我在一個數組中得到他們的主鍵

只需使用unnest()並且不要浪費時間加入contacts表格：

SELECT id
FROM   unnest('{1,10,20,1557,5000,15057}'::int[]) id  -- actual array (not list)
EXCEPT ALL SELECT contact_id FROM case_contacts
EXCEPT ALL SELECT contact_id FROM case_payments
EXCEPT ALL SELECT contact_id FROM invoices;

除非您需要驗證存在，否則您需要加入：

SELECT id
FROM   unnest(arr) id
JOIN   contacts c USING (id)
EXCEPT ALL ...

可用技術概述：

• 選擇其他表中不存在的行

我不建議使用NOT IN (subselect)，這通常是最慢的替代方法，並且帶有 NULL 值的陷阱。您的NOT EXISTS變體看起來非常好。

引用自：https://dba.stackexchange.com/questions/143243