如何解決 MySQL 缺乏 FULL OUTER JOIN 功能的問題？

MySQL 本身不支援 FULL OUTER JOIN。 這會在嘗試使用它時導致語法錯誤。

解：使用 UNION

要複製 FULL OUTER JOIN 的行為，MySQL 需要使用 UNION 子句的解決方法。這結合了 LEFT JOIN 和 RIGHT JOIN 的結果。

實現此目標的方法如下：

<code class="language-sql">SELECT airline, airports.icao_code, continent, country, province, city, website 
FROM airlines 
LEFT JOIN airports ON airlines.iaco_code = airports.iaco_code
LEFT JOIN cities ON airports.city_id = cities.city_id
LEFT JOIN provinces ON cities.province_id = provinces.province_id
LEFT JOIN countries ON cities.country_id = countries.country_id
LEFT JOIN continents ON countries.continent_id = continents.continent_id
UNION
SELECT airline, airports.icao_code, continent, country, province, city, website 
FROM airlines 
RIGHT JOIN airports ON airlines.iaco_code = airports.iaco_code
RIGHT JOIN cities ON airports.city_id = cities.city_id
RIGHT JOIN provinces ON cities.province_id = provinces.province_id
RIGHT JOIN countries ON cities.country_id = countries.country_id
RIGHT JOIN continents ON countries.continent_id = continents.continent_id;</code>

此查詢有效地合併了LEFT JOIN（左表中的所有行，匹配右表中的行）和RIGHT JOIN（右表中的所有行，匹配左表中的行）的結果，提供像FULL OUTER JOIN 一樣的完整資料集。 請注意，重複項將透過 UNION 操作刪除。 如果需要保留重複項，請使用 UNION ALL.

以上是如何解決 MySQL 缺乏 FULL OUTER JOIN 功能的問題？的詳細內容。更多資訊請關注PHP中文網其他相關文章！