Full outer join implementation in MySQL 5.7+

Question

I have a scenario with MySQL joins Look at the 2 tables given below

Table1:

id   A   B
1    10  25
2    13  24
3    15  20

Table 2:

id   C   D
2    12  21
3    10  18
4    13  23

My problem is to join these tables in a which returns all the rows in both tables with corresponding values

Table1 JOIN Table2:

id     A     B     C     D
1     10     25   null  null
2     13     24    12    21
3     15     20    10    18
4     null   null  13    23

I think we can implement this using full outer join. But in MySQL 5.7+ full join is not supported. I searched many similar questions. But all are saying about left and right join with union. That solution wont help me, because i have totally 7 tables like this. I need to get all the data from each table in this way. So if i use left and right join with union my query will become a huge query. So is there any other way to get this ?

Why do you have 7 tables? – Strawberry Mar 19 '19 at 08:49 — Strawberry, Mar 19 '19 at 08:49

score -1 · Answer 1 · answered Mar 19 '19 at 06:07

-1

Use left and rigt join with UNION ALL

select a.id,A,B, C, D
from table1 a left join table2 b n a.id=b.id
union 
select a.id,A,B, C, D
from table1 a right join table2 b n a.id=b.id

answered Mar 19 '19 at 06:07

Fahmi

37,315
5
22
31

if there are a number of tables like this and i want to join all these tables in a similar way what will i do ? If i use this left and right join with union all it will create a big query – Anas Mar 19 '19 at 06:10
Obviously, right JOIN is not actually required, but whatever floats your boat – Strawberry Mar 19 '19 at 08:50
This solution is wrong. – The Impaler Feb 15 '22 at 16:28

Full outer join implementation in MySQL 5.7+

Table1:

Table 2:

Table1 JOIN Table2:

1 Answers1