Postgresql – Two left joins where one exists

join;postgresqlquery

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.

Best Answer

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').

Related Question