outer join in mysql not providing the results

Question

Hypothetical data -

tbl1 -

id	date	value1
101	2021-01-01	200
101	2021-01-03	400

tbl2 -

id	date	value2
101	2021-01-01	600
101	2021-01-02	900

My expected result is -

id	date	value1	value2
101	2021-01-01	200	600
101	2021-01-02	NaN	900
101	2021-01-03	400	NaN

select * from (select * from tbl1 where id in
(another query)) t1
left join tbl2 as t2 on t1.id = t2.id and t1.date = t2.date
union all
select * from (select * from tbl1 where id in
(another query)) t1
right join tbl2 as t2 on t1.id = t2.id and t1.date = t2.date
where t1.id is null and t1.date is null

I am unable to figure out where am I going wrong.

followed Pablo's answer: https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql — Maxima, Apr 16 '21 at 07:45
My bad, heard about mysql don't having a full outer join, but forgot — Turo, Apr 16 '21 at 07:46

Tim Biegeleisen · Accepted Answer · 2021-04-16T07:55:26.147

4

I think you might be overcomplicating your union query:

SELECT t1.id, t1.date, t1.value1, t2.value2
FROM tbl1 t1
LEFT JOIN tbl2 t2 ON t1.id = t2.id AND t1.date = t2.date
UNION ALL
SELECT t2.id, t2.date, t1.value1, t2.value2
FROM tbl1 t1
RIGHT JOIN tbl2 t2 ON t1.id = t2.id AND t1.date = t2.date
WHERE t1.id IS NULL
ORDER BY id, date;

Demo

edited Apr 16 '21 at 07:55

answered Apr 16 '21 at 07:48

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 2 · answered Apr 16 '21 at 08:10

Collect all present (id, `date`) pairs then join source data to it:

SELECT id, `date`, tbl1.value1, tbl2.value2
FROM ( SELECT id, `date` FROM tbl1
       UNION 
       SELECT id, `date` FROM tbl2 ) combined
LEFT JOIN tbl1 USING (id, `date`)
LEFT JOIN tbl2 USING (id, `date`);

fiddle

The solution assumes that (id, `date`) is unique over each separate source table. If not then some aggregation must be used (SUM or MAX, depends on the logic).

outer join in mysql not providing the results

2 Answers2

Demo