Query improvements without UNION

Question

I have the following scenario. A users table, a records table and a memberships table.

A user can be associated to zero or more records either directly with the records table

records.user_id = users.id

or via a membership (the membership table contains user_id and record_id)

memberships.user_id = users.id

Let's assume I want to load all the records managed by the user. The query directly translates into

SELECT records.* FROM records WHERE records.user_id = ?
UNION
SELECT records.* FROM records
INNER JOIN memberships ON memberships.record_id = records.id
WHERE memberships.user_id = ?

Is there a way to combine these two queries into a single one without UNION?

I tried starting from:

SELECT records.* 
FROM records 
LEFT OUTER JOIN memberships ON memberships.record_id = records.id 
WHERE records.user_id = ? OR memberships.user_id = ?

But it also returns the records where the JOIN matches for other user memberships. Any hint?

Answer 1

Use OR with EXISTS or IN:
(the IN version only if the memberships.record_id column is not nullable)

SELECT r.* 
FROM records AS r
WHERE r.user_id = ?
   OR EXISTS 
      ( SELECT 1 
        FROM memberships AS m
        WHERE m.record_id = r.id 
          AND m.user_id = ?
      ) ;

SELECT r.* 
FROM records AS r
WHERE r.user_id = ?
   OR r.id IN  
      ( SELECT m.record_id 
        FROM memberships AS m
        WHERE m.user_id = ?
      ) ;

This way you also do not get the duplicate rows of records that the join introduces (and then are get rid in the UNION DISTINCT part of your query.)

---

You could use the LEFT join query but you would have to either add DISTINCT or GROUP BY the primary key of the records table to avoid the duplicate rows:

SELECT r.* 
FROM records  AS r
  LEFT OUTER JOIN memberships AS m
    ON m.record_id = r.id 
WHERE r.user_id = ? 
   OR m.user_id = ?
GROUP BY r.id ;