How to Emulate FULL JOIN in MySQL?

Emulating FULL JOIN in MySQL

In MySQL, FULL OUTER JOIN is not directly supported. However, a workaround can be implemented to simulate its functionality.

A typical approach involves using two UNION ALL queries:

SELECT
  t_13.value AS val13,
  t_17.value AS val17
FROM
  t_13
LEFT JOIN
  t_17
ON
  t_13.value = t_17.value
UNION ALL
SELECT
  t_13.value AS val13,
  t_17.value AS val17
FROM
  t_13
RIGHT JOIN
  t_17
ON
  t_13.value = t_17.value
WHERE
  t_13.value IS NULL
ORDER BY
  COALESCE(val13, val17)
LIMIT 30

This query performs a LEFT JOIN initially, followed by a UNION ALL with a RIGHT JOIN. The WHERE condition ensures that rows not matched in the LEFT JOIN are included. Finally, COALESCE is used to handle NULL values.

By combining these steps, MySQL can emulate a FULL OUTER JOIN, enabling the retrieval of all rows from both tables, even if they do not have matching values.

The above is the detailed content of How to Emulate FULL JOIN in MySQL?. For more information, please follow other related articles on the PHP Chinese website!