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

--(1)--
SELECT * FROM Income_o I FULL JOIN Outcome_o O   
    
ON I.point = O.point AND I.date = O.date;

--(2)--
SELECT * FROM Income_o I LEFT JOIN Outcome_o O
      ON I.point = O.point AND I.date = O.date   
UNION  
SELECT * FROM Income_o I RIGHT JOIN Outcome_o O   
      ON I.point = O.point AND I.date = O.date;

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:

--(3)--
SELECT * FROM Income_o I LEFT JOIN Outcome_o O
      ON I.point = O.point AND I.date = O.date   
UNION ALL  
SELECT NULL, NULL, NULL,* FROM Outcome_o O   
WHERE NOT EXISTS (SELECT 1 FROM Income_o I   
                  WHERE I.point = O.point AND I.date = O.date);

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):

SELECT * FROM Income_o I LEFT JOIN Outcome_o O
      ON I.point = O.point AND I.date = O.date   
UNION ALL  
SELECT * FROM Income_o I RIGHT JOIN Outcome_o O   
      ON I.point = O.point AND I.date = O.date  
WHERE NOT EXISTS (SELECT 1 FROM Income_o I   
                  WHERE I.point = O.point AND I.date = O.date);