-
-
Save hashcode86/c17defadd065fdc8369b785f8a719b2e to your computer and use it in GitHub Desktop.
How to do FULL [OUTER] JOIN in MySQL (MariaDB)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- First method | |
| SELECT * FROM t1 | |
| LEFT JOIN t2 ON t1.id = t2.id | |
| UNION | |
| SELECT * FROM t1 | |
| RIGHT JOIN t2 ON t1.id = t2.id | |
| -- Second Method | |
| -- The query above works for special cases where a FULL OUTER JOIN operation would not produce any duplicate rows. | |
| -- The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. | |
| -- We can avoid introducing duplicate rows by using an anti-join pattern for the second query, | |
| -- and then use a UNION ALL set operator to combine the two sets. In the more general case, | |
| -- where a FULL OUTER JOIN would return duplicate rows, we can do this | |
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment