Question 183761 in Mysql Question posted in
The official MySQL documentation can be found here.

How to do FULL OUTER JOIN with three tables in MySQL?

Consider the following tables:

create table `t1` (
  `date` date,
  `value` int
);

create table `t2` (
  `date` date,
  `value` int
);

create table `t3` (
  `date` date,
  `value` int
);

insert into `t1` (`date`, `value`)
values ("2022-01-01", 1),
       ("2022-03-01", 3),
       ("2022-04-01", 4);
       
insert into `t2` (`date`, `value`)
values ("2022-01-01", 1),
       ("2022-02-01", 2),
       ("2022-04-01", 4);

insert into `t3` (`date`, `value`)
values ("2022-01-01", 1),
       ("2022-02-01", 2),
       ("2022-03-01", 3);

The t1 table is missing 2022-02-01 date and t2 is missing 2022-03-01 and t3 is missing 2022-04-01. I want to join these three tables so that it produces the following result:

| t1.date    | t1.value | t2.date    | t2.value | t3.date    | t3.value | 
|            |          |            |          |            |          |
| 2022-01-01 | 1        | 2022-01-01 | 1        | 2022-01-01 | 1        |
| null       | null     | 2022-02-01 | 2        | 2022-02-01 | 2        |
| 2022-03-01 | 3        | null       | null     | 2022-03-01 | 3        |
| 2022-04-01 | 4        | 2022-04-01 | 4        | null       | null     |

I know how to do full outer join with two tables but with three or more tables things are more complicated. I’ve tried queries like this but it didn’t produce the result I want:

select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date`
left join `t3` on `t3`.`date` = `t2`.`date` or `t3`.`date` = `t1`.`date`

union

select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date`
right join `t3` on `t3`.`date` = `t2`.`date` or `t3`.`date` = `t1`.`date`;
Tags:

6

Answers


  1. Chosen as BEST ANSWER
    0

    I achieved the result I wanted with a different approach:

    select 
  t1.date,
  t1.value,
  t2.date,
  t2.value,
  t3.date,
  t3.value
from (
  select t1.date from t1
  union
  select t2.date from t2
  union
  select t3.date from t3
) t
left join t1 on t1.date = t.date
left join t2 on t2.date = t.date
left join t3 on t3.date = t.date;

  2. 0

    You are on right track but you have to consider all combinations – 3 tables – 4 union clauses.

    -- t1 full join with t2
select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date` 
union 
select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date` 

-- t1 full join with t3
union 
select *
from `t1`
left join `t3` on `t3`.`date` = `t1`.`date` 

union 
select *
from `t1`
right join `t3` on `t3`.`date` = `t1`.`date`
    Login or Signup to reply.
  3. 0

    I am not sure what are you trying to do; the left join is used to keep all the rows from your left table and bring all the matching rows from your right table.

    It means: since you do not have the ‘2022-02-01’ date in the t1 it will never appear in your query by using a left join.

    Now if you want to combine all the results from three tables, a union between the three tables will do the trick:

    select *
    from t1
    union
    select *
    from t2
    union
    select *
    from t3;

    If you want it ordered just make a subquery:

    select *
    from (
    select *
    from t1
    union
    select *
    from t2
    union
    select *
    from t3
    ) as allData
    order by date;

    I hope it helps.

    Login or Signup to reply.
  4. 0

    This is it:

    select a.`date`,a.value,a2.`date`,a2.value,a3.`date`,a3.value
from  date_range b left join `t1` a 
on  a.`date`=b.dates
left join `t2` a2
on a2.`date`=b.dates
left join `t3` a3
on a3.`date`=b.dates;

    http://sqlfiddle.com/#!9/0fbc85/9

    This is only concerned for these three tables only..
    You also would need to create a date range table for all the ranges presenting in the dates present in all the tables!!!

    Login or Signup to reply.
  5. 0

    You can do (t1 full join t2) full join t3 with a full join implementation of choice. Provided joining on primary keys so duplicates must be excluded

    with t12 as (
    select coalesce(t2.`date`, t1.`date`) 'date', t1.`date` t1date, t1.`value` t1val ,t2.`date` t2date, t2.`value` t2val
    from t1
    left join t2 on t2.`date` = t1.`date` 
    union
    select coalesce(t2.`date`, t1.`date`) 'date', t1.`date` t1date, t1.`value` t1val ,t2.`date` t2date, t2.`value` t2val
    from t1
    right join t2 on t2.`date` = t1.`date`     
)
select coalesce(t12.`date`, t3.`date` ) d, t1date, t1val ,t2date, t2val, t3.`date` t3date, t3.`value` t3val 
from t12
left join t3 on t12.`date` = t3.`date` 
union
select coalesce(t12.`date`, t3.`date` ) d, t1date, t1val ,t2date, t2val, t3.`date` t3date, t3.`value` t3val 
from t12
right join t3 on t12.`date` = t3.`date` 
order by d

    db-fiddle

    Login or Signup to reply.
  6. 0

    First you have to UNION ALL dates from all three tables. Then you sort and distinct this union, and finally you left join all three tables consequently.

    In your last solution I am still missing DISTINCT and ORDER BY.

    This should do the result you wanted:

    Select * From
(
Select distinct `date` From (
Select distinct `date` From t1
Union All
Select distinct `date` From t2
Union All
Select distinct `date` From t3 ) f
Order By f.`date`
) a
LEFT JOIN t1 ON t1.`date` = a.`date`
LEFT JOIN t2 ON t2.`date` = a.`date`
LEFT JOIN t3 ON t3.`date` = a.`date`
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search