SQL 连接 : add custom constraint in JOIN clause

Question

我想选择首选语言(如果存在)，否则选择默认语言。

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

v_program_items 表如下所示:

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

我使用两个 JOIN(在同一张表上)和一个 CASE/WHEN。
是否可以只使用一个 JOIN ？

Answer 1

您拥有的联接很好，并且在使用索引时表现非常好 - 应该是 UNIQUE 索引(或 PK):

CREATE UNIQUE INDEX ON v_program_items (activity_id, language);

使用COALESCE在 SELECT 列表中，如评论中建议的“PM 77-1”:

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;

在 Postgres 11 中，只有当您的表 v_program_items 很大时，才考虑覆盖索引:

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

相关:

• Do covering indexes in PostgreSQL help JOIN columns?

无论哪种方式，当只选择一行(或几行)时，低相关子查询应该更快。也很简单:

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