Full outer join?

[SQL newbie]

I have used ms sql server 2008 express in school. I am new to sql.

I have practiced using some of these joins from this example url here:

http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx
One of the joins i used was->

SELECT *
FROM Table_A as A
full outer Table_B as B
ON A.PKey = B.PKey
where A.PKey IS NULL OR B.Pkey IS NULL;

Given i moved to windows 7 i was not able to get ms sql on my machine
so i decided to use mysql.

But i got a syntax error and find the key word full is not allowed...?
According to mysql syntax in the url below, its not present....

http://dev.mysql.com/doc/refman/5.0/en/join.html

So the question is, can i still do a full join in mysql but with
different sql from what i gave above?

Here is the code i used:

TABLE_A
PKey Value
---- ----------
1 FOX
2 COP
3 TAXI
6 WASHINGTON
7 DELL
5 ARIZONA
4 LINCOLN
10 LUCENT

create table table_a
(
PKey int(5),
value varchar(20)
);

insert sampledb.table_a(PKey, value)
value(1, "fox");
insert sampledb.table_a(PKey, value)
value(2, "cop");
insert sampledb.table_a(PKey, value)
value(3, "taxi");
insert sampledb.table_a(PKey, value)
value(6, "washington");
insert sampledb.table_a(PKey, value)
value(7, "dell");
insert sampledb.table_a(PKey, value)
value(5, "arizona");
insert sampledb.table_a(PKey, value)
value(4, "lincoln");
insert sampledb.table_a(PKey, value)
value(10, "lucent");

TABLE_B
PKey Value
---- ----------
1 TROT
2 CAR
3 CAB
6 MONUMENT
7 PC
8 MICROSOFT
9 APPLE
11 SCOTCH

create table table_b
(
PKey int(5),
value varchar(20)
);

insert sampledb.table_b(PKey, value)
value(1, "trot");
insert sampledb.table_b(PKey, value)
value(2, "car");
insert sampledb.table_b(PKey, value)
value(3, "cab");
insert sampledb.table_b(PKey, value)
value(6, "monument");
insert sampledb.table_b(PKey, value)
value(7, "pc");
insert sampledb.table_b(PKey, value)
value(5, "microsoft");
insert sampledb.table_b(PKey, value)
value(4, "apple");
insert sampledb.table_b(PKey, value)
value(10, "scotch");
Andrew

[SQL newbie]

[Jerry Stuckle]

Read down the page. There is a workaround in the user notes.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[David Portas]

"Jerry Stuckle" <jstu...@attglobal.net> wrote in message
news:hei0dm$quk$2...@news.eternal-september.org...

>
> Read down the page. There is a workaround in the user notes.
>

Unfortunately the given workarounds using UNION are not truly FULL OUTER
joins because they eliminate duplicate rows that a real full outer join
would not. In most cases that maybe won't be a problem but I think it's just
worth noting that reproducing the true behaviour of FULL is a bit more
complex than those examples imply.

--
David Portas

[Captain Paralytic]

On Nov 27, 12:49 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> "Jerry Stuckle" <jstuck...@attglobal.net> wrote in message

So use UNION ALL

[Jerry Stuckle]

Eggzaggly.

[SQL newbie]

==Aha! Thank you for those who posted.
I will try it out
Andrew

> jstuck...@attglobal.net
> ==================

[Gints Plivna]

On 25 nov., 02:43, SQL newbie <nordies_1...@yahoo.com> wrote:
> I have used ms sql server 2008 express in school. I am new to sql.
>
> I have practiced using some of these joins from this example url here:
>
> http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx
> One of the joins i used was->

You can read my paper SQL join types here http://www.gplivna.eu/papers/sql_join_types.htm
It has examples for all join types for Oracle, SQL Server and MySQL
(of course for those that particular DB supports).

Gints Plivna
http://www.gplivna.eu

[edward]

I wrote an article about full join in mysql some time ago (in spanish)

http://disemq21.blogspot.com/2009/08/another-mysql-full-join.html

SELECT b.host,
one.host AS one, one.ip,
two.host AS two, two.ip,
three.host AS three, three.ip
FROM (
SELECT DISTINCT a.host AS host
FROM (
SELECT idone AS id, host, ip
FROM one
UNION ALL
SELECT idtwo AS id, host, ip
FROM two
UNION ALL
SELECT idthree AS id, host, ip
FROM three
) a ORDER BY a.host
) b
LEFT JOIN one ON one.host=b.host
LEFT JOIN two ON two.host=b.host
LEFT JOIN three ON three.host=b.host

+----------+---------+-----------+----------+-----------+---------
+-----------+
| host | one | ip | two | ip | three |
ip |
+----------+---------+-----------+----------+-----------+---------
+-----------+
| as400 | as400 | - | NULL | NULL | NULL |
NULL |
| linux-01 | NULL | NULL | linux-01 | - | NULL |
NULL |
| linux01 | linux01 | 1.2.3.4 | NULL | NULL | linux01 |
1.3.2.4 |
| osx2 | osx2 | 1.1.1.1 | osx2 | 1.1.1.1 | osx2 |
1.1.1.100 |
| solaris | solaris | 1.1.1.100 | solaris | 1.1.1.100 | solaris |
1.1.1.100 |
| win-fw | NULL | NULL | win-fw | 1.2.3.100 | win-fw |
1.2.2.100 |
+----------+---------+-----------+----------+-----------+---------
+-----------+

On 4 dic, 08:55, Gints Plivna <gints.pli...@gmail.com> wrote:
> On 25 nov., 02:43, SQL newbie <nordies_1...@yahoo.com> wrote:
>
> > I have used ms sql server 2008 express in school. I am new to sql.
>
> > I have practiced using some of these joins from this example url here:
>
> >http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx
> > One of the joins i used was->
>

> You can read my paper SQL join types herehttp://www.gplivna.eu/papers/sql_join_types.htm