SQL query – Records has at least one of the relationships specified – alternative?

I have 4 tables person, employments, properties, memberships

How can I filter people that have at least one of the three relationships above?

What I tried:

SELECT * 
FROM people p
WHERE p.id IN (SELECT e.person_id FROM employments e WHERE e.person_id = p.id)
   OR p.id IN (SELECT pr.person_id FROM person_properties pr WHERE pr.person_id = p.id)
   OR p.id IN (SELECT m.person_id FROM union_memberships m WHERE m.person_id = p.id)

Seems to be working, but it looks odd, and I am not sure if it’s the most efficient way of doing it.

Any tips?

Answer

Your IN clauses are redundant. You have a correlation clause and then are checking the same column for IN. This could confuse the optimizer (but probably not).

This is a simpler way to express the logic:

SELECT * 
FROM people p
WHERE EXISTS (SELECT 1 FROM employments e WHERE e.person_id = p.id) OR
      EXISTS (SELECT 1 FROM person_properties pr WHERE pr.person_id = p.id) OR
      EXISTS (SELECT 1 FROM union_memberships m WHERE m.person_id = p.id);

I would be surprised, however, if this had a noticeable affect on performance.

For performance, you want indexes on:

• employments(person_id)
• person_properties(person_id)
• union_memberships(person_id).