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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 =

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

memberships.user_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 = ?
SELECT records.* FROM records
INNER JOIN memberships ON memberships.record_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 = 
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 '14 at 13:01
Yes, when the records had 1 ore more memberships, the same record was returned multiple times. – Simone Carletti May 12 '14 at 13:28
Can a user be associated via both ways (via records.user_id and via the memberships table)? – ypercubeᵀᴹ May 12 '14 at 14:26
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)

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

FROM records AS r
WHERE r.user_id = ?
   OR 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:

FROM records  AS r
  LEFT OUTER JOIN memberships AS m
    ON m.record_id = 
WHERE r.user_id = ? 
   OR m.user_id = ?
share|improve this answer
According to the EXISTS statement is very inefficient. Does this mean this query is less efficient than UNION? – Simone Carletti May 12 '14 at 13:22
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 '14 at 13:27
You can certainly try all 3 versions of the query (UNION, EXISTS, LEFT JOIN) in your environment and test for efficiency. – ypercubeᵀᴹ May 12 '14 at 13:33
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 '14 at 13:48
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 '14 at 13:56

Your Answer


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.