How Can I Simulate a FULL OUTER JOIN in MySQL?

Simulate FULL OUTER JOIN in MySQL

MySQL itself does not support FULL OUTER JOIN. However, you can simulate it using a combination of LEFT JOIN, RIGHT JOIN, and set operators.

LIMITED FULL OUTER JOIN

For cases where FULL OUTER JOIN does not produce duplicate rows, you can use:

<code class="language-sql">SELECT * 
FROM t1
LEFT JOIN t2 
ON t1.id = t2.id
UNION
SELECT * 
FROM t1
RIGHT JOIN t2 
ON t1.id = t2.id</code>

The UNION operator eliminates duplicate rows introduced by this query pattern.

General FULL OUTER JOIN

For the common case where duplicate rows may occur, use:

<code class="language-sql">SELECT * 
FROM t1
LEFT JOIN t2 
ON t1.id = t2.id
UNION ALL
SELECT * 
FROM t1
RIGHT JOIN t2 
ON t1.id = t2.id
WHERE t1.id IS NULL</code>

The UNION ALL operator combines the results of two JOINs, and the additional WHERE clause ensures that only rows with t1.id NULL (not matched in the LEFT JOIN) are added to the final result, thus eliminating duplicates.

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