ASNI full outer join
I am
playing with the ANSI Full out join syntax using IDS 9.40.UC7 on RH
linux.
However, I don't know if I am reading the manual correctly. The manual
states:
From the IBM Informix manuals
ANSI FULL OUTER Joins
The FULL keyword specifies a join in which the result set includes all
the rows from the Cartesian product for which the join condition is
true, plus all the rows from each table that do not match the join
condition.
So I created my test example:
create temp table odd
(
invoice integer,
value char(1)
) with no log;
create temp table even
(
invoice integer,
value char(1)
) with no log;
insert into odd values (1, "A");
insert into odd values (3, "B");
insert into odd values (5, "C");
insert into odd values (7, "D");
insert into even values (2, "A");
insert into even values (4, "B");
insert into even values (6, "C");
insert into even values (8, "D");
I execute the following query:
select e.*, o.* from even e full outer join odd o on e.invoice = o.invoice;
The result is:
invoice value invoice value
2 A
4 B
6 C
8 D
1 A
3 B
5 C
7 D
Which is correct.
However, if I delete all rows in table "even", I don't get the rows from
table odd?
Is correct functionality? From the above definition I would say no
since the definition says "all rows that do not match the join condition"
Any clarification would be great.
Thanks
Peter
----------
CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,is for
the sole use of the intended recipient(s), even if addressed incorrectly, and
may contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy or delete all
copies of the original message and all attachments, including deletion from
the trash or equivalent folder. Thank you.
[demime 1.01d removed an attachment of type text/x-vcard which had a name of
pdiazdeleon.vcf]
On 11/15/05,
Peter J Dia.... <pdiazdeleon@infinityhealthcare.com> wrote:
>
> I am playing with the ANSI Full out join syntax using IDS 9.40.UC7 on RH
> linux.
> However, I don't know if I am reading the manual correctly. The manual
> states:
>
> From the IBM Informix manuals
>
> ANSI FULL OUTER Joins
> The FULL keyword specifies a join in which the result set includes all
> the rows from the Cartesian product for which the join condition is
> true, plus all the rows from each table that do not match the join
> condition.
>
>
> So I created my test example:
>
> create temp table odd
> (
> invoice integer,
> value char(1)
> ) with no log;>
> create temp table even
> (
> invoice integer,
> value char(1)
> ) with no log;>
> insert into odd values (1, "A");
> insert into odd values (3, "B");
> insert into odd values (5, "C");
> insert into odd values (7, "D");>
> insert into even values (2, "A");
> insert into even values (4, "B");
> insert into even values (6, "C");
> insert into even values (8, "D");>
> I execute the following query:
> select e.*, o.* from even e full outer join odd o on e.invoice = o.invoice
> ;
>
> The result is:
> invoice value invoice value
>
> 2 A
> 4 B
> 6 C
> 8 D
> 1 A
> 3 B
> 5 C
> 7 D
>
> Which is correct.
Agreed.
However, if I delete all rows in table "even", I don't get the rows from
> table odd?
> Is correct functionality?
No. You should get all the rows from 'odd'.
From the above definition I would say no
> since the definition says "all rows that do not match the join condition"
>
> Any clarification would be great.
Please contact IBM/Informix Tech Support.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/