Jump to content

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


pallevillesen

Recommended Posts

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.