0

Basically, I have table a, which has a type and type_id and want to join on either two different tables where there is a hit.

So basically, if table b has a match, return the record or if table c has a match, return that record (essentially two inner joins with an OR); just one of them has to match. The below query seems to work... however I feel like this is a very naive solution that will actually result in me running into some issues.

SELECT
    a.*
FROM
    a
    LEFT JOIN b ON a.type_id = b.id
    AND b.user_id = 123
    AND a.type = 'dog'
    LEFT JOIN c ON a.type_id = c.type_id
    AND c.user_id = 123
    AND a.type = 'cat'
WHERE
    b.id IS NOT NULL
    OR c.type_id IS NOT NULL;

Basically, I want all the records where the user exists in either table.

4

You could rewrite with OR and two EXISTS subqueries and compare performance with your query:

SELECT
    a.*
FROM
    a
WHERE
    EXISTS
    ( SELECT FROM b
      WHERE a.type_id = b.id
        AND b.user_id = 123
        AND a.type = 'dog'
    )
 OR EXISTS
    ( SELECT FROM c
      WHERE a.type_id = c.type_id
        AND c.user_id = 123
        AND a.type = 'cat'
    ) ;

or:

SELECT
    a.*
FROM
    a
WHERE
    a.type = 'dog'
    AND EXISTS
    ( SELECT FROM b
      WHERE a.type_id = b.id
        AND b.user_id = 123
    )
 OR a.type = 'cat'
    AND EXISTS
     ( SELECT FROM c
       WHERE a.type_id = c.type_id
         AND c.user_id = 123
    ) ;

Your query seems correct, assuming that there are unique constraints or indexes on b (user_id, id) or on b (id) - and c (user_id, type_id).

It does however looks somewhat obfuscated - it is not clear without careful reading that it restricts the result to rows that have WHERE a.type_id IN ('dog', 'cat').

  • ah nice one! That does look nicer, however the language ORM im using (ecto for elixir) doesn't actually allow subqueries in where clauses (..yet). So just wondering if my solution is actually going to give any wrong results or if it should be ok? I'll mark the answer as solved beucase what you have posted will work for 99% of people – Harrison Lucas Nov 13 '18 at 10:44
  • Your query seems fine, yes (assuming some unique constraints) on b and c. – ypercubeᵀᴹ Nov 13 '18 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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