FULL OUTER JOIN in MySQL

[Anon]

hi,

I have two tables:

table_1:

id_1

1
2
3

table_2:

id_2

3
4
5

I need to perform some kind of join to get the following result:

id_1 | id_2

1 |NULL
2 |NULL
3 |NULL
NULL |3
NULL |4
NULL |5

Someone told me i should use FULL OUTER JOIN, which didn't help me much, because there is no such join in MySQL (or is there ?)

Leszek

[[deleted]]

You're not joining the tables at all, just printing the output one after the other.

[ljoncz]

Whatever you call it, I need such result in ONE query. Don't ask me why.

[saloon12yrd]

not asking why sounds funny 🙂
actually I can not think of a single situation where I wouldn't be able to do two seperate queries... maybe a little more background information will help.

Dominique

[Anon]

do you mean you want the output to be:

id_1 | id_2

1 |NULL
2 |NULL
3 |3
NULL |4
NULL |5

not:

id_1 | id_2

1 |NULL
2 |NULL
3 |NULL
NULL |3
NULL |4
NULL |5

because that first example is a full outer join, the second is just concatenating two tables together.

If you are after a full outer join not many database programs can do it natively, but I have made an a bunch of access queries which when run together perform a simulated full outer join. Email me if you need a copy.
Cheers.

[Anon]

I have the exact same need - a full outer join in MySql. Is it possible?

I'm trying to do some counting and group by's at the same time. Using two queries would be awkward and inefficient. Even if it didn't prevent me - why wouldn't MySQL support a full outer join?

I'm actually trying to join more than two tables at the same time as well.

Also, I agree, this would be a full outer join:

id_1 | id_2

1 |NULL
2 |NULL
3 |3
NULL |4
NULL |5

[[deleted]]

"Using two queries would be awkward and inefficient."

A full outer join would just make the database do two queries internally, so 'inefficient' does not really apply.

"why wouldn't MySQL support a full outer join?"

Because MySQL is a crap 'database' that puts speed over functionality.

[Anon]

Well this is how I implemented it in Microsoft Access, the mySQL should be fairly easy to adapt.

This relies on you having two tables called table1 and table2. This could probably be adapted to use more than two tables, but I only had a need for two when I wrote it.

It also needs both tables to have a columns called code (this is the data is compares to determine what to join)

qLeftOuterJoin:
SELECT Table1.Code AS TheCode, Table1., Table2.
FROM Table1 LEFT JOIN Table2 ON Table1.Code=Table2.Code;

qRestrictedOuterJoin:
SELECT Table2.Code AS TheCode, Table1., Table2.
FROM Table1 RIGHT JOIN Table2 ON Table1.Code=Table2.Code
WHERE (((Table1.Code) Is Null));

qSimulatedFullOuterJoin
SELECT [qLeftOuterJoin].TheCode,[qLeftOuterJoin].
FROM [qLeftOuterJoin]
UNION SELECT [qRestrictedRightOuterJoin].TheCode,[qRestrictedRightOuterJoin].
FROM [qRestrictedRightOuterJoin];

If anyone has any suggestions/improvements on this code please post them here.

AUTOMATA.