loading..
   English
00:31

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

Console
Execute
  1. --(1)--
  2. SELECT * FROM Income_o I FULL JOIN Outcome_o O
  3. ON I.point = O.point AND I.date = O.date;
that display income and outcome on every working day on each item in single line (Recycled materials company). It
can be rewritten as:

Console
Execute
  1. --(2)--
  2. SELECT * FROM Income_o I LEFT JOIN Outcome_o O
  3. ON I.point = O.point AND I.date = O.date
  4. UNION
  5. SELECT * FROM Income_o I RIGHT JOIN Outcome_o O
  6. 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:

Console
Execute
  1. --(3)--
  2. SELECT * FROM Income_o I LEFT JOIN Outcome_o O
  3. ON I.point = O.point AND I.date = O.date
  4. UNION ALL
  5. SELECT NULL, NULL, NULL,* FROM Outcome_o O
  6. WHERE NOT EXISTS (SELECT 1 FROM Income_o I
  7. 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):

Console
Execute
  1. SELECT * FROM Income_o I LEFT JOIN Outcome_o O
  2. ON I.point = O.point AND I.date = O.date
  3. UNION ALL
  4. SELECT * FROM Income_o I RIGHT JOIN Outcome_o O
  5. ON I.point = O.point AND I.date = O.date
  6. WHERE NOT EXISTS (SELECT 1 FROM Income_o I
  7. WHERE I.point = O.point AND I.date = O.date);
Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100