如何编写没有空的联接两个表的查询？

问：

我有 2 张桌子

飞机场

+ -------------------------------------+-------------+-------------+
| ID                                   | IATA        | ICAO        | 
+--------------------------------------+-------------+-------------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c65 | CHH         | SPPY        | 
+--------------------------------------+-------------+-------------+

机场翻译

+ -------------------------------------+------------------------------------ -+------+-------------+
| ID                                   | PARENTID                             | LANG | VALUE       |
+--------------------------------------+--------------------------------------+------+-------------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c66 | 01d491eb-7d00-410c-9c19-eb23d83f9c65 | ESP  | CHACHAPOYAS |
+--------------------------------------+--------------------------------------+------+-------------+
| eb06eb88-0530-4051-b4eb-3a04a01bcafe | 01d491eb-7d00-410c-9c19-eb23d83f9c65 | ENG  | CHACHAPOYAS |
+--------------------------------------+--------------------------------------+------+-------------+

以下查询返回简单联接：

SELECT
public."Airports"."ID",
public."Airports"."IATA", 
public."Airports"."ICAO",
CASE WHEN public."AirportTranslation"."Lang" = 'ESP' THEN public."AirportTranslation"."Value" ELSE null END as "ESP",
CASE WHEN public."AirportTranslation"."Lang" = 'EN' THEN public."AirportTranslation"."Value" ELSE null END as "EN"
FROM public."Airports" JOIN 
public."AirportTranslation" ON

结果如下所示：

+ -------------------------------------+-----+------+-------------+-----------+
| ID                                   | ITA | ICAO | ESP         | EN        |
+--------------------------------------+-----+--------------------+-----------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c65 | CHH | SPPY | null        |CHACHAPOYAS|
+--------------------------------------+-----+--------------------+-----------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c65 | CHH | SPPY | CHACHAPOYAS |null       |
+--------------------------------------+-----+------|-------------+-----------+

是否可以编写查询来获取没有空值的结果，也不使用 WITH、交叉表等？

+ -------------------------------------+-----+------+-------------+-----------+
| ID                                   | ITA | ICAO | ESP         | EN        |
+--------------------------------------+-----+--------------------+-----------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c65 | CHH | SPPY | CHACHAPOYAS |CHACHAPOYAS|
+--------------------------------------+-----+--------------------+-----------+