0

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?

3
  • Does the LEFT join query give you the correct result - but some rows are duplicated? Commented May 12, 2014 at 13:01
  • Yes, when the records had 1 ore more memberships, the same record was returned multiple times. Commented May 12, 2014 at 13:28
  • Can a user be associated via both ways (via records.user_id and via the memberships table)? Commented May 12, 2014 at 14:26

1 Answer 1

4

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 ;
8
  • According to techonthenet.com/sql/exists.php the EXISTS statement is very inefficient. Does this mean this query is less efficient than UNION? Commented May 12, 2014 at 13:22
  • 5
    No, that statement: "SQL Statements that use the SQL EXISTS Condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table." is false. Most DBMS implement EXISTS quite efficiently and do not rerun the subquery for every row of the external table. I would remove that site from my bookmarks. Commented May 12, 2014 at 13:27
  • 1
    You can certainly try all 3 versions of the query (UNION, EXISTS, LEFT JOIN) in your environment and test for efficiency. Commented May 12, 2014 at 13:33
  • 1
    No really. the difference is very small. And there are several other ways to write the query. You could try UNION with EXISTS in the 2nd part (not INNER JOIN). Or using IN instead of EXISTS and possibly some other ways. But since it runs in about 30 ms, isn't is good enough? Commented May 12, 2014 at 13:48
  • 1
    I suggest then you do the tests with larger tables. Add some fake data, thousands or millions of rows (as many as you expect to have) and then experiment with the various queries. Commented May 12, 2014 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.