Skip to content
Fix Code Error

How to do a FULL OUTER JOIN in MySQL?

March 13, 2021 by Code Error
Posted By: Anonymous

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

Solution

You don’t have FULL JOINS on MySQL, but you can sure emulate them.

For a code SAMPLE transcribed from this SO question you have:

with two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

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
Answered By: Anonymous

Related Articles

  • SQL query return data from multiple tables
  • Pandas Merging 101
  • data.table vs dplyr: can one do something well the other…
  • sql query to find priority jobs
  • generate days from date range
  • SQL find sum of entries by date including previous date
  • Union of multiple Database queries with same parameters
  • Extract from Union type where discriminator is also a Union
  • SQL JOIN and different types of JOINs
  • insert tables in dataframe with years from 2000 to 20018…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

Increase heap size in Java

Next Post:

CSS fixed width in a span

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Get code errors & solutions at akashmittal.com
© 2022 Fix Code Error