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?

ypercubeᵀᴹ · Accepted Answer · 2014-05-12T14:24:46.787

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 ;

Query improvements without UNION

1 Answers1

Linked