sql - 简化/重用此SQL查询中的子查询

我有以下SQL查询：

SELECT "users".* FROM "users"
WHERE (
  users.id IN (SELECT manager_id FROM managerships WHERE managerships.employee_id = 1234) OR
  users.custom_id IN (SELECT manager_custom_id FROM managerships WHERE managerships.employee_id = 1234) OR
  users.saml_id IN (SELECT manager_saml_id FROM managerships WHERE managerships.employee_id = 1234) OR
  users.email IN (SELECT manager_email FROM managerships WHERE managerships.employee_id = 1234)
)

现在我认为应该有一种方法，从我的managerships表中选择一次并重用它，比如：

SELECT manager_id, manager_email, manager_custom_id, manager_saml_id FROM managerships WHERE managerships.employee_id = 1234

我怎样才能做到？谢谢您！

最佳答案

您可以尝试将users和managerships表连接在一起：

SELECT t1.*
FROM "users" t1
INNER JOIN managerships t2
    ON t1.id        = t2.manager_id OR
       t1.custom_id = t2.manager_custom_id OR
       t1.saml_id   = t2.manager_saml_id OR
       t1.email     = t2.manager_email
WHERE t2.employee_id = 1234;

这种方法除了更易于阅读之外，还有一个优点，就是假设您已经定义了两个表的索引，那么就可以在这两个表上使用索引。