SELECT statement

Using multiple tables in a query

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

Console

Execute

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

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

    Scheme

Console

Execute

--(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;

--(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;

    Scheme

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:

Console

Execute

--(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);

--(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);

    Scheme

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

Console

Execute

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

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

    Scheme