How to Work Around MySQL's Lack of FULL OUTER JOIN Functionality?

MySQL does not natively support FULL OUTER JOIN. This results in a syntax error when attempting to use it.

The Solution: Using UNION

To replicate the behavior of a FULL OUTER JOIN, MySQL requires a workaround using the UNION clause. This combines the results of a LEFT JOIN and a RIGHT JOIN.

Here's how to achieve this:

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

This query effectively merges the results of a LEFT JOIN (all rows from the left table, matching rows from the right) and a RIGHT JOIN (all rows from the right table, matching rows from the left), providing the complete set of data as a FULL OUTER JOIN would. Note that duplicates will be removed by the UNION operation. If you need to preserve duplicates, use UNION ALL.

The above is the detailed content of How to Work Around MySQL's Lack of FULL OUTER JOIN Functionality?. For more information, please follow other related articles on the PHP Chinese website!