SQL JOIN: add custom constraint in JOIN clause

I want to select the preferred language if it exists and the default language otherwise.

SELECT  a.code,
        case
          when vpi.program_items is not null then vpi.program_items  else vpi2.program_items
        end
FROM              activity a                        
                    LEFT OUTER JOIN v_program_items vpi ON vpi.activity_id = a.id AND vpi.language = 'fr_BE'
                    LEFT OUTER JOIN v_program_items vpi2 ON vpi2.activity_id = a.id AND vpi2.language = 'fr'
WHERE   a.id = 62170

The v_program_items table looks as :

 - ID      | language|  program_items
 - 62170   | fr      |  Présentation du club et des machines¤Briefing avant le vol¤45 minutes de vol en ULM 
 - 62170   |  fr_BE  |  Un vol en ULM (45 min)  

I use two JOIN (on the same table) and one CASE/WHEN.
Is it possible to use only one JOIN ?

Answer

The joins you have are fine and perform very well with an index – should be a UNIQUE index (or PK):

CREATE UNIQUE INDEX ON v_program_items (activity_id, language);

Use COALESCE in the SELECT list, like “PM 77-1” suggested in a comment:

SELECT a.code, COALESCE(v1.program_items, v2.program_items) AS program_items
FROM   activity a                        
LEFT   JOIN v_program_items v1 ON v1.activity_id = a.id AND v1.language = 'fr_BE'
LEFT   JOIN v_program_items v2 ON v2.activity_id = a.id AND v2.language = 'fr'
WHERE  a.id = 62170;

In Postgres 11, and only if your table v_program_items is big, consider a covering index:

CREATE UNIQUE INDEX ON v_program_items (activity_id, language) INCLUDE (program_items);

Related:

• Do covering indexes in PostgreSQL help JOIN columns?

Either way, while selecting only a single row (or a few), lowly correlated subqueries should be even faster. Simple, too:

SELECT a.code
     , COALESCE((SELECT program_items FROM v_program_items WHERE activity_id = a.id AND language = 'fr_BE')
              , (SELECT program_items FROM v_program_items WHERE activity_id = a.id AND language = 'fr')) AS program_items
FROM   activity a
WHERE  a.id = 62170