Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 4 down vote accepted

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 ;
share|improve this answer
    
According to techonthenet.com/sql/exists.php the EXISTS statement is very inefficient. Does this mean this query is less efficient than UNION? –  Simone Carletti May 12 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. –  ypercube May 12 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. –  ypercube May 12 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? –  ypercube May 12 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. –  ypercube May 12 at 13:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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