Simplify/reuse subqueries in this SQL query

Question

I have the following SQL query:

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)
)

Now I believe there should be a way to just select once from my managerships table and reuse it, something like:

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

How can I achieve that? Thank you!

selvarajmas · Accepted Answer · 2017-04-04 02:38:28Z

14

You can use WITH to take the aggregations out into a CTE (common table expression):

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

Pls refer also https://www.postgresql.org/docs/9.1/static/queries-with.html

edited Apr 4, 2017 at 2:38

answered Apr 4, 2017 at 2:17

selvarajmas

1,64315 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gringo Suave Over a year ago

What if multiple statements need to use m?

Tim Biegeleisen · Accepted Answer · 2017-04-04 02:13:33Z

2

You can try just joining the users and managerships tables together:

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;

This approach, in addition to being easier to read, has the added of advantage of possibly being able to use indices on the two tables, assuming you have defined them.

edited Apr 4, 2017 at 2:13

answered Apr 4, 2017 at 2:07

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

1 Comment

Jan Klimo Over a year ago

Thank you, Tim! I went with your approach and it works great. I just had to GROUP the results to get unique entries.

Gordon Linoff · Accepted Answer · 2017-04-04 02:14:46Z

1

You can write the query as something like this:

SELECT u.*
FROM users u CROSS JOIN
     managerships m
WHERE m.employee_id = 1234 AND
      (u.id = m.manager_id OR
       u.custom_id = m.manager_custom_id OR
       u.saml_id = m.manager_saml_id OR
       u.email = m.manager_email 
      );

I don't think there is any improvement on performance. Any version of this query really needs an index on managerships(employee_id). With this index, your query and this should have pretty much the same performance.

Note: You can express this using on -- it does the same thing. Because of the OR the conditions cannot really be optimized, which is why I'm putting them in the WHERE clause.

answered Apr 4, 2017 at 2:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Jan Klimo Over a year ago

Thank you, Gordon!

Collectives™ on Stack Overflow

Simplify/reuse subqueries in this SQL query

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related