Full Outer Join

from the Artful Common Queries page

A FULL OUTER join between tables a and b retrieves:
  • all rows from a, with matching rows or nulls from b, and
  • all rows from b, with matching rows or nulls from a
so for these tables: 
DROP TABLE IF EXISTS a,b;
CREATE TABLE a(id int,name char(1));
CREATE TABLE b(id int,name char(1));
INSERT INTO a VALUES(1,'a'),(2,'b');
INSERT INTO b VALUES(2,'b'),(3,'c');
SELECT * FROM a;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
SELECT * FROM b;
+------+------+
| id   | name |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
a full outer join returns: 
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | b    |
| NULL | NULL |    3 | c    |
+------+------+------+------+
MySQL does not support FULL OUTER JOIN. How to emulate it? If the joining keys of each table are unique, you can just UNION left and right joins: 
SELECT * FROM a LEFT JOIN b ON a.id=b.id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id=b.id;
But suppose the tables to be joined have duplicate rows, and you wish your result to preserve them. For example, add a duplicate row to table a: 
INSERT INTO a VALUES(1,'a');
Now UNION removes the duplicate row you want preserved in the result. How to get back the desired duplicates? A FULL OUTER JOIN consists of:
  • an INNER JOIN between a and b to catch row matches between a and b,
  • a LEFT EXCLUSION JOIN from a to b to catch rows that are in a and not in b,
  • a RIGHT EXCLUSION JOIN from b to a to catch rows in b that are not in a.
In SQL: 
SELECT * FROM a INNER JOIN b ON a.id=b.id
UNION ALL
SELECT * FROM a LEFT JOIN b ON a.id=b.id WHERE b.id IS NULL
UNION ALL
SELECT * FROM a RIGHT JOIN b ON a.id=b.id WHERE a.id IS NULL
But the first two joins—the inner join, and the left exclusion join—are logically equivalent to a left outer join, so we can write: 
SELECT * FROM a LEFT JOIN b ON a.id=b.id
UNION ALL
SELECT * FROM a RIGHT JOIN b ON a.id=b.id WHERE a.id IS NULL;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | b    |
|    1 | a    | NULL | NULL |
| NULL | NULL |    3 | c    |
+------+------+------+------+
The need for a full outer join often arises in a complicated context. A MySQL user posted this SQL in the MySQL Newbie forum ... 
SELECT
     dim_dates.`year2` AS year,
     dim_dates.`date` AS DimDate,
     dim_dates.`day_name` AS DimDay,
     dim_dates.`holidays` AS DimHolidays,
     dim_dates.`week_in_year` AS DimWeek,
     dim_dates.`month_number` AS DimMonthNr,
     dim_dates.`month_name` AS DimMonth,
     dim_dates.`quarter_name` AS DimQuarter,
     dim_dates.`year_quarter` AS DimYearQ,
     dim_dates.`date_key` AS DimDateKey,
     S_orders_total.`orders_id` AS OrdersId,
     S_orders_total.`title` AS OrdersTitle,
     S_orders_total.`value` AS OrdersValue,
     S_orders.`date_purchased` AS OrdersDate,
     S_projection1.`p_budget_date` AS BudgetDate,
     S_orders.`customers_id` AS CustId,
     S_orders.`customers_name` AS CustName,
     S_orders.`customers_company` AS CustCompany,
     S_orders.`customers_street_address` AS CustStreet,
     S_orders.`customers_city` AS CustCity,
     S_orders.`customers_postcode` AS CustPostcode,
     S_orders.`customers_state` AS CustState,
     S_orders.`customers_country` AS CustCountry,
     S_orders.`customers_telephone` AS CustTelephone,
     S_orders.`customers_email_address` AS CustEmail,
     S_customers_groups.`customers_group_name` AS CustomersGroup,
     S_projection1.`p_sales_trends_adjust` AS PsalesTrendsAdjust,
     S_projection1.`p_s_bar_prod` AS PShopBar,
     S_orders_status_A.`orders_status_name` AS OrdersStatus
FROM
     `S_orders` S_orders INNER JOIN `S_orders_total` S_orders_total ON S_orders.`orders_id` = S_orders_total.`orders_id`
     INNER JOIN `dim_dates` dim_dates ON S_orders.`date_purchased` = dim_dates.`date`
     INNER JOIN `S_customers` S_customers ON S_orders.`customers_id` = S_customers.`customers_id`
     INNER JOIN `S_orders_status` S_orders_status_A ON S_orders.`orders_status` = S_orders_status_A.`orders_status_id`
     INNER JOIN `S_customers_groups` S_customers_groups ON S_customers.`customers_group_id` = S_customers_groups.`customers_group_id`
     FULL OUTER JOIN `S_projection1` S_projection1 ON dim_dates.`date` = S_projection1.`p_budget_date`
WHERE
     S_orders.`orders_status` = 3
 AND S_orders_total.`title` IN (("Sub-Total:"),("Subtotal :"),("Sub-Totaal:"));
The SQL fails because MySQL has no idea what FULL OUER JOIN means. It's also unclear whether the formulation is correct. How to find out? A three-step: (i) save the result of the non-full-outer-join portion of the query to a temp table ... 
drop table if exists temp;
create table temp
SELECT
     dim_dates.`year2` AS year,
     dim_dates.`date` AS DimDate,
     dim_dates.`day_name` AS DimDay,
     dim_dates.`holidays` AS DimHolidays,
     dim_dates.`week_in_year` AS DimWeek,
     dim_dates.`month_number` AS DimMonthNr,
     dim_dates.`month_name` AS DimMonth,
     dim_dates.`quarter_name` AS DimQuarter,
     dim_dates.`year_quarter` AS DimYearQ,
     dim_dates.`date_key` AS DimDateKey,
     S_orders_total.`orders_id` AS OrdersId,
     S_orders_total.`title` AS OrdersTitle,
     S_orders_total.`value` AS OrdersValue,
     S_orders.`date_purchased` AS OrdersDate,
   --  S_projection1.`p_budget_date` AS BudgetDate,
     S_orders.`customers_id` AS CustId,
     S_orders.`customers_name` AS CustName,
     S_orders.`customers_company` AS CustCompany,
     S_orders.`customers_street_address` AS CustStreet,
     S_orders.`customers_city` AS CustCity,
     S_orders.`customers_postcode` AS CustPostcode,
     S_orders.`customers_state` AS CustState,
     S_orders.`customers_country` AS CustCountry,
     S_orders.`customers_telephone` AS CustTelephone,
     S_orders.`customers_email_address` AS CustEmail,
     S_customers_groups.`customers_group_name` AS CustomersGroup,
   --  S_projection1.`p_sales_trends_adjust` AS PsalesTrendsAdjust,
   --  S_projection1.`p_s_bar_prod` AS PShopBar,
     S_orders_status_A.`orders_status_name` AS OrdersStatus
FROM S_orders 
INNER JOIN S_orders_total                     ON S_orders.orders_id = S_orders_total.orders_id
INNER JOIN dim_dates                          ON S_orders.date_purchased = dim_dates.date
INNER JOIN S_customers                        ON S_orders.customers_id = S_customers.customers_id
INNER JOIN S_orders_status S_orders_status_A  ON S_orders.orders_status = S_orders_status_A.orders_status_id
INNER JOIN S_customers_groups                 ON S_customers.customers_group_id = S_customers_groups.customers_group_id
WHERE S_orders.`orders_status` = 3
  AND S_orders_total.`title` IN ("Sub-Total:","Subtotal :","Sub-Totaal:") ;
(ii) verify that this result is correct (iii) run & test ... 
select 
  temp.*, 
  S_projection1.p_budget_date, 
  S_projection1.p_sales_trends_adjust,
  S_projection1.p_s_bar_prod 
from temp
left join s_projection1 on temp.date = s_projection1.p_budget_date
union  
select 
  temp.*, 
  S_projection1.p_budget_date, 
  S_projection1.p_sales_trends_adjust,
  S_projection1.p_s_bar_prod 
from temp
right join s_projection1 on temp.date = s_projection1.p_budget_date ;
Once this works, the correct (but not yet optimised) query is (iii) with temp replaced by (i) in parentheses aliased as temp. Why doesn't MySQL implement FULL OUTER JOIN syntax? We don't know.

Last updated 12 Oct 2024