FULL JOIN and MySQL
FULL OUTER JOIN is not supported in MySQL. We can assume that this is "excess" operation, because it appears by the combination of left and right outer joins. For example, a query
can be rewritten as:
From a logical point of view, these queries are equivalent. Both output the days when he was in-and outcomes, and the days when there was no one from operations (missing values are replaced by NULL). However, in terms of performance the second request is losing the first half of the valuation of the plan. This is due to the fact that the UNION operation cause sorting, which is missing in first query. Sorting is necessary for removing duplicates, because left and right joins, both contain a string corresponding to the inner join, i.e. case when there is an income, and outcomes. Therefore, if instead of writing UNION writes UNION ALL, then these rows will be present in the result set in duplicate.
Nevertheless, to get a plan close to the value of FULL JOIN, we need to get rid of the sort. For example, we can use UNION ALL, but in one of the unifying query to exclude rows that correspond to an inner join:
Note that obviously missing values, which appeared in the right join of solution (2), are formed explicitly by specifying NULL-values. If for some reason, you do not want to explicitly specify NULL instead of the join, you can leave the join, but it will give a more expensive plan, although it will be cheaper plan then plan with ordering (2):