Full Outer Join & Union MySQL

Question

I am currently having to simulate a full outer join on two tables (right outer + left outer), and the use a union to get rid of the duplicates.

I was wondering, because I have many tables to do this to, and I want to end up with a single table in the end, is there a better way to do this.

This is what I'm currently doing:

create table `table+left` as(
select table1.col1, table1.col2, table1.col3, table2.col2 as `alias`
from table1
left outer join table2
on table1.col1 = table2.col1
);

create table `table+right` as(
select table1.col1, table1.col2, table1.col3, table2.col2 as `alias`
from table1
right outer join table2
on table1.col1 = table2.col1
);

create table `table1+table2` as
select * from `table+left`
union
select * from `table+right`;

I'm just relieved that I don't have to work with this naming policy :-| — Strawberry, Jan 24 '17 at 11:32
what do you want to accomplish? add sample data and result you want to achieve. — Siraj ul Haq, Jan 24 '17 at 11:47
Is the select in the create table 'table+right' correct it - looks like you would get nulls for table1.col1, table1.col2, table1.col3 where there is no match in table1? — P.Salmon, Jan 24 '17 at 12:11
@SirajulHaq I want to append on column on the end of the first table and then remove duplicates. — Daniel Lawton, Jan 24 '17 at 12:45

score 0 · Accepted Answer · answered Jan 24 '17 at 13:16

0

There is no need to create the first 2 tables given

drop table if exists table1;
drop table if exists table2;
create table table1 (col1 int,col2 int,col3 int);
create table table2 (col1 int,col2 int,col3 int);

insert into table1 values
(1,1,1),(2,2,2);
insert into table2 values
(1,1,1),(3,3,3);

This query returns exactly the same result as yours

MariaDB [sandbox]> drop table if exists `table1+table2`;
Query OK, 0 rows affected (0.12 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> create table `table1+table2` as
    -> select table1.col1, table1.col2, table1.col3, table2.col2 as `alias`
    -> from table1
    -> left outer join table2
    -> on table1.col1 = table2.col1
    -> union
    -> select table1.col1, table1.col2, table1.col3, table2.col2 as `alias`
    -> from table1
    -> right outer join table2
    -> on table1.col1 = table2.col1;
Query OK, 3 rows affected (0.32 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from `table1+table2`;
+------+------+------+-------+
| col1 | col2 | col3 | alias |
+------+------+------+-------+
|    1 |    1 |    1 |     1 |
|    2 |    2 |    2 |  NULL |
| NULL | NULL | NULL |     3 |
+------+------+------+-------+
3 rows in set (0.00 sec)

answered Jan 24 '17 at 13:16

P.Salmon

17,104
2
12
19

I was just about to come back and edit my question! Here's what I got: `create table `table1+table2`as select table1.col1, table1.col2, table2.col2 as 'alias' from table1 left outer join table2 on table1.col1 = table2.col1 union select table1.col1, table1.col2, table2.col2 as 'alias' from table1 right outer join table2 on table1.col1 = table2.col1; ` – Daniel Lawton Jan 24 '17 at 13:20
Hey, I have just used this method to try and make the table, however it has so far taken 24 hours, and it still hasn't completed. Each table has around 30 million rows. – Daniel Lawton Jan 25 '17 at 13:19
Do you have any idea why? – Daniel Lawton Jan 25 '17 at 15:46
Do you have any indexes on table1 and table2? – P.Salmon Jan 25 '17 at 15:47
Yeah I've got indexes on the same column for both tables @P.Salmon – Daniel Lawton Jan 26 '17 at 15:57

Full Outer Join & Union MySQL

1 Answers1