Jean:

If you need to know what rows in either table have no match in the other you
have to use a UNION. (It is because such a construct is functionally and
semantically equivalent to a 'both' outer join that Informix only supports
one-way outer joins.) So:

SELECT ...
FROM tab1 AS a LEFT OUTER JOIN tab2 AS b
ON a.key1 = b.key1 ...
WHERE b.key1 IS NULL
UNION
SELECT ...
FROM tab2 AS b LEFT OUTER JOIN tab1 AS a
ON a.key1 = b.key1 ...
WHERE a.key1 IS NULL;

Art S. Kagel

----- Original Message -----
From: Jean Sagi <jeansagi@myrealbox.com>
At: 11/17 17:00

> Thanks Art and "Whatever...", for your comments...
>
> I'm aware of LEFT OUTER JOIN in IDS 9.3 and 9.4 (althoug it would be
> great if FULL OUTER join exists... but that's another story).
>
> But there is something funny... I thought that the left outer join with
> null filters were not fixed on 9.3 and 9.4...
>
> I tryed the approach "whatever" shows some months ago but I only get
> nulls on the right table... ie:
>
> ...
> from a left outer join b on (a.x = b.x)
> where b.y is null
> ...
>
> I also tryed
>
> ...
> from a left outer join b on (a.x = b.x and b.y is null)
> where b.x is null
> ...
>
> Maybe I do something wrong...
>
> I'll try with "wahtever" notes....
>
>
> Art S. Kagel wrote:
> > On Sun, 16 Nov 2003 05:54:29 -0500, Jean Sagi wrote:
> >
> >
> >
> >>Art S. Kagel wrote:
> >>
> >>
> >>>... "EXISTS sub-query" is a correlated sub-query which is always slower
than
> >>>a join and can "always [can] be converted into a join"...
> >>
> >>EXISTS sub-query always [can] be converted into a join... I can imgine how
to
> >>do it.
> >>
> >>But
> >>
> >>Can, NOT EXISTS sub-query, be converted into a join?
> >>
> >>If so, how? (I can use this...)
> >>
> >
> > WHATEVER has nailed the answer, except that IDS supports LEFT OUTER joins
not
> > RIGHT ones and it does not support sub-query-instead-of-table in the FROM
> > clause. As is stated in that message you use ANSI-92 syntax for a LEFT
OUTER
> > JOIN with the join conditions in the ON clause and a filter for a NULL value
> in
> > some NOT NULL column in the dependent table (as WHATEVER implies one of the
> > JOIN columns is an excellent candidate for the filter.
> >
> > Art S. Kagel
> >
> >>Chucho!
> >>
> >
> >
> >
>
> --
>
>
> Atte,
>
>
> Jesús Antonio Santos Giraldo
> jeansagi@myrealbox.com
> jeansagi@netscape.net




sending to informix-list