PostgreSQL 同表左外连接多表

Question

我有一个表需要与两个不同的表进行左外连接。当我将表两次放入查询中并在 where 子句中将其与它自身连接时(如下面的句子)它起作用了。我认为这不应该是正确的方法。如何编写 select 语句和外连接多个不同表的表？

SELECT cols."COLUMN_NAME"
     , COALESCE(translations."COLUMN_LANG_TITLE",cols."COLUMN_TITLE") AS "COLUMN_TITLE" 
     , options."OPTION_DESC"
  FROM 

"EXTAPP_SETUP"."OBJECT_COLUMNS_TAB" cols LEFT OUTER JOIN "EXTAPP_SETUP"."COLUMN_LANG_TITLES_TAB" translations
        ON translations."EXTAPP_ID" = cols."EXTAPP_ID" and translations."OBJECT_NAME" = cols."OBJECT_NAME" and translations."COLUMN_NAME" = cols."COLUMN_NAME" and translations."LANGUAGE_CODE" = 'fr',

"EXTAPP_SETUP"."OBJECT_COLUMNS_TAB" cols2 LEFT OUTER JOIN "EXTAPP_SETUP"."COL_FIX_OPTIONS_TAB" options
        ON options."EXTAPP_ID" = cols2."EXTAPP_ID" and options."OBJECT_NAME" = cols2."OBJECT_NAME" and options."COLUMN_NAME" = cols2."COLUMN_NAME" 

WHERE cols."EXTAPP_ID" = cols2."EXTAPP_ID" and cols."OBJECT_NAME" = cols2."OBJECT_NAME" and cols."COLUMN_NAME" = cols2."COLUMN_NAME" 

Answer 1

您可以使用后续的左连接。由于您并没有真正使用 cols2 表，因此可以将其从查询中删除。这也将消除 where 子句，因为您不需要将 cols 表与其自身连接

SELECT cols."COLUMN_NAME"
  , COALESCE(translations."COLUMN_LANG_TITLE",cols."COLUMN_TITLE") AS "COLUMN_TITLE" 
  , options."OPTION_DESC"
FROM "EXTAPP_SETUP"."OBJECT_COLUMNS_TAB" cols 
LEFT OUTER JOIN "EXTAPP_SETUP"."COLUMN_LANG_TITLES_TAB" translations
  ON translations."EXTAPP_ID" = cols."EXTAPP_ID" 
    and translations."OBJECT_NAME" = cols."OBJECT_NAME" 
    and translations."COLUMN_NAME" = cols."COLUMN_NAME" 
    and translations."LANGUAGE_CODE" = 'fr' 
LEFT OUTER JOIN "EXTAPP_SETUP"."COL_FIX_OPTIONS_TAB" options
  ON options."EXTAPP_ID" = cols."EXTAPP_ID" 
    and options."OBJECT_NAME" = cols."OBJECT_NAME" 
    and options."COLUMN_NAME" = cols."COLUMN_NAME"