[INFO:]How to do a FULL OUTER JOIN in Mysql 3.23

[pallevillesen]

I have two tables, a and b which contains the following:

 

A:

+----+

| id |

+----+

| a  |

| b  |

| c  |

+----+



B:

+----+

| id |

+----+

| b  |

| c  |

| d  |

+----+

 

And I would like to do a search returning ALL rows from both tables. I.e.

 

A

B

C

D

 

(Somebody might want something like

 

A NULL

B B

C C

NULL D

 

Which is easy, but this example is the simplest.

 

create temporary table t1unique as

select a.id from a LEFT OUTER JOIN b on a.id = b.id 

where b.id IS NULL;



This will get all rows with ONLY a value in table A.



create temporary table t2unique as

select b.id from a RIGHT OUTER JOIN b on a.id = b.id 

WHERE a.id IS NULL;



This will get all rows with ONLY a value in table B.



create temporary table t3 as

select a.id from a, b where a.id = b.id;



This will get all rows with matching values in A and B.



INSERT INTO t3 select * from t1unique;

INSERT INTO t3 select * from t2unique;



Putting the first two queries into the third one....



select * from t3 order by id;



Tadaa...



drop table t1unique;

drop table t2unique;

drop table t3;



Cleaning up.

 

Ugly code, I know - but it\'s working.

 

mysql> select * from t3 order by id;

+----+

| id |

+----+

| a  |

| b  |

| c  |

| d  |

+----+

4 rows in set (0.00 sec)

[effigy]

:shock: you love those temporary\'s P

[pallevillesen]

Nope.... I actually quite dislike them, but I couldn\'t figure out how to this is one long ugly sql statement (ahem.. that is in mysql, where subselects and full outer joins are nonexistent (in version 3.34))...

 

And I was freaked out by the lack of results when googling for MYSQL FULL OUTER JOIN....

 

I think we could need a tutorial on temporary tables and what they are good for (i.e. subselects and outer joins tips and trick)... Anyone?

 

P.

[Uranium-235]

Wow, now I need a drink